Problem with Docmd.TransferText

[hedgracer]

I have the following code:


Private Sub cmdImportRanFile_Click()

strCnxn = set_connection()
Set cnxn = New ADODB.Connection
cnxn.Open strCnxn
strSQL_Execute = "dbo.Ran_BillingDelete"
cnxn.Execute strSQL_Execute, , adExecuteNoRecords

Dim dkg As Variant, str As String
dkg = Application.FileDialog(msoFileDialogFilePicker).Show
str = Application.FileDialog(msoFileDialogFilePicker).SelectedItems(1)


DoCmd.TransferText acImportDelim, "RanBillingImportSpecificationA", "Ran_Billing", str

DoCmd.Hourglass True
strCnxn = set_connection()
Set cnxn = New ADODB.Connection
cnxn.Open strCnxn
strSQL_Execute = "dbo.Ran_BillingUpdate"
cnxn.Execute strSQL_Execute, , adExecuteNoRecords

DoCmd.Hourglass False
End Sub

The problem are is the DoCmd.TransferText statement. When the code is run I get the following error on this statement:

Runtime Error '3001': Invalid Argument.

This code runs perfectly in another database. I have inspected the import specification and compared it to the other database to ensure that they are the same and they are. I checked the references to make sure they are the same in both databases and they are. Can anyone shed some light on what I am possibly missing? Any help is appreciated. Thanks.

 

[lameid]

Does the table exist in the database where it fails? What if you import the table from the working database does it work?

 

[hedgracer]

The table is a linked SQL Server 2005 table. The Access 2007 database is merely a front end.

 

[lameid]

In that case can you open the linked table directly in the database? You might just try re-linking it for good measure.

 

[hedgracer]

I can open the linked table in the database. I tried re-linking the table twice and this did not solve the problem.

 

[lameid]

For grins, try renaming the existing table and see if it will import into a new Jet table.... this will at least isolate whether it has anything to do with the table which is my hunch but maybe I'm wrong...

Another possibility is the database is corrupt... so the old standby's there are compact/repair and importing everything into a new database file.

Another possiblity, does the code compile, are there missing references?