Search code examples
vbams-accessimport

Access VBA Convert Linked Table to Local

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.

Solution

  • 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?