Access VBA Convert Linked Table to Local

Question

I am running into a problem and can use some advice. I am linking to an excel spreadsheet and then i am trying to convert that link into a table.

my very basic function is as follows

dim mypath as string

docmd.transferspreadsheet aclink, acspreadsheettypeexcel112xml, "importeddata", mypath, true
runcommand acCmdConvertLinkedTableToLocal

this gives me a runtime error 2046 The command or action 'ComvertLinkedTableToLocal' isn't available now.

So, the reason for doing the link and then the conversion is when done manually, it will get rid of all the conversion errors that a normal import will do and then I have my basic VBA scripts which seem to run far quicker in Access than Excel.

Again, any help is duly duly appreciated

Groundhog.

Answer 1

I believe you need to have a specific linked table selected in order to run the acCmdConvertLinkedTableToLocal command. Try something like this after linking:

DoCmd.SelectObject acTable, "importeddata", True
DoCmd.RunCommand acCmdConvertLinkedTableToLocal

Alternatively, have you tried creating an empty table that matches the spreadsheet structure (i.e. using text fields instead of numeric fields) so that you don't get the import errors when you import instead of link?

Answer 2

Thank you for sharing this code example. I had to make one addition to get this to work correctly for me. I was getting a runtime message that the command was not available before adding a DoEvents after the SelectObject and prior to acCmdConvertLinkedTableToLocal.