Error when trying to run acCmdConvertLinkedTableToLocal after TransferText

Question

Using VBA I import Orders from a CSV File to a linked table

Then I convert the table to local

If I try to combine these steps in to one sub I get the error "The command or action ConvertLinkedTableToLocal isn't available now"

Here is the procedure using a click event

Private Sub Import_Orders_Click()

If SysCmd(acSysCmdGetObjectState, 0, "Tbl_Import_Orders") <> 0 Then

   MsgBox "Tbl_Import_Orders is open or not accessible - Process will End"

   Exit Sub

End If

Dim fDialog As FileDialog, result As Integer

Set fDialog = Application.FileDialog(msoFileDialogFilePicker)

fDialog.AllowMultiSelect = False

fDialog.Title = "Select a file"

fDialog.InitialFileName = "E:\"

If fDialog.Show = -1 Then

    Dim db As DAO.Database

    Set db = CurrentDb

    On Error Resume Next

    db.TableDefs.delete "Tbl_Import_Orders"

    On Error GoTo 0

    db.TableDefs.Refresh

    DoCmd.TransferText acLinkDelim, , "Tbl_Import_Orders", fDialog.SelectedItems(1), True

End If

    DoCmd.SelectObject acTable, "Tbl_Import_Orders", True

    DoCmd.RunCommand acCmdConvertLinkedTableToLocal

End Sub

I've tried several suggestions I found including timers, Refreshing the Table Defs again before running the command and others (just can't remember them all at the moment)

If I run the Convert command on a separate button it works fine, but I'd like to run this all as one to automate it.

Any ideas would be appreciated, thanks

Matt

Answer 1

Well it wasn't onedrive for a change

I rewound to my original code and added a deleteobject to delete the import table after checking if it was open

Works now without issue

I wouldn't have gotten there without your help and I learned some from both of you

Thanks again

Matt

Answer 2

Unfortunately I still get the "ConvertLinkedTableToLocal isn't available now" error

Can you check me and see if I have this correct

Thanks

Private Sub btn_Import_Orders_Click()

' Check if Table Is already open

If SysCmd(acSysCmdGetObjectState, acTable, "Tbl_Import_Orders") <> 0 Then

MsgBox "Tbl_Import_Orders is open or not accessible - Process will End"

Exit Sub

End If

Dim fDialog As FileDialog, result As Integer

Set fDialog = Application.FileDialog(msoFileDialogFilePicker)

fDialog.AllowMultiSelect = False

fDialog.Title = "Select a file"

fDialog.InitialFileName = "E:\OneDrive\Storage\_eBay\Reports"

fDialog.Filters.Clear

fDialog.Filters.Add "CSV files", "*.csv"

fDialog.Filters.Add "All files", "*.*"

If fDialog.Show = -1 Then

Dim db As DAO.Database 

Set db = CurrentDb 

On Error Resume Next 

db.TableDefs.delete "Tbl\_Import\_Orders" 

On Error GoTo 0 

db.TableDefs.Refresh 

'Import 

DoCmd.TransferText acLinkDelim, , "Tbl\_Import\_Orders", fDialog.SelectedItems(1), True 

' Wait for linked table to be fully imported 

Do While CurrentDb.TableDefs("Tbl\_Import\_Orders").Fields.Count = 0 

    DoEvents 

Loop 

Else

Exit Sub 

End If

DoCmd.SelectObject acTable, "Tbl\_Import\_Orders", True 

DoCmd.RunCommand acCmdConvertLinkedTableToLocal 

db.Close 

Set db = Nothing 

MsgBox "Orders Import Complete" 

End Sub

Answer 3

Sorry I didn't reply sooner, I never received a notification

Going to try it and let you know how it works

Answer 4

Set conn = CreateObject("ADODB.Connection")

strPath = "C:\YourFolderPath" ' Update this with your actual folder path

With conn

.Provider = "Microsoft.ACE.OLEDB.12.0"

.ConnectionString = "Data Source=" & strPath & "\yourDatabase.accdb;"

.Open

End With

sql = "SELECT * INTO inputTable FROM [Text;Database=" & strPath & ";FMT=CSVDelimited].[yourfile.csv]"

conn.Execute sql

conn.Close

Set conn = Nothing

Answer 5

It appears that the timing or order of the orders may have anything to do with the problem. It's possible that the linked table is triggering the ConvertLinkedTableToLocal command before the data import is complete.

Adding a delay or waiting for the linked table to finish importing before attempting to convert it to a local table is one method to fix this. To add a delay to your code, follow these steps:

Private Sub Import_Orders_Click()

If SysCmd(acSysCmdGetObjectState, 0, "Tbl_Import_Orders") <> 0 Then MsgBox "Tbl_Import_Orders is open or not accessible - Process will End" Exit Sub End If

Dim fDialog As FileDialog Dim db As DAO. Database Dim tblName As String

Set fDialog = Application.FileDialog(msoFileDialogFilePicker) Set db = CurrentDb

fDialog.AllowMultiSelect = False fDialog.Title = "Select a file" fDialog.InitialFileName = "E:"

If fDialog.Show = -1 Then ' Delete existing linked table On Error Resume Next db. TableDefs.Delete "Tbl_Import_Orders" On Error GoTo 0

' Refresh TableDefs db. TableDefs.Refresh

' Import data into linked table tblName = fDialog.SelectedItems(1) DoCmd.TransferText acLinkDelim, , "Tbl_Import_Orders", tblName, True

' Wait for linked table to be fully imported Do While CurrentDb.TableDefs("Tbl_Import_Orders"). Fields.Count = 0 DoEvents Loop Else Exit Sub End If

' Convert linked table to local DoCmd.SelectObject acTable, "Tbl_Import_Orders", True DoCmd.RunCommand acCmdConvertLinkedTableToLocal

End Sub

With this patch, a loop is added that prevents the linked table from being attempted to be converted to a local table until it contains at least one field, signalling that it has been fully imported. This ought to make it more likely that the conversion command will be carried out when it's supposed to.