How to use a Import Specification file in a different Database 1

[vakjay]

I have two databases. The first database contains the switchboard, forms, reports, queries, and all the code. The second database which is linked to the first database contains all the tables.
I want to import data to a table in the second database using "DoCmd.TransferText(acImportDelim, ImportSpecification, TableName, FileName, HASFIELDNAMES)" command. I have created a "ImportSpecification". I know that the import specification is attached to the relevant database. In my case it should be attached to the second database which contains all the tables. But the code is looking for the importspecication file in the first database where the code is running from.
Can some one tell me a method to refrencing a specification file in the second database in the above command.

 

[RobHudson]

Hi

I don't know if you can reference a spec in a different db, but would importing it the other DB be any good??

In the DB that requires the spec, do a Get External Data and select to see the further options. Choose to include import specs.

Hope that helps
Rob

 

[p27br]

You could open the back end database programmatically and run the import routine on it.

Sub Import_Input(strFile As String)
Const TextSpecs = "myfile"

On Error GoTo ImportClientInput_Error

Dim appaccess As Access.Application

Set appaccess = New Access.Application
With appaccess
.OpenCurrentDatabase strDataDB, True
.DoCmd.TransferText acImportDelim, TextSpecs, TextSpecs, strFile
.CloseCurrentDatabase
End With
Set appaccess = Nothing
MsgBox "Import Successful"

Exit_ImportClientInput:

Exit Sub

ImportClientInput_Error:
MsgBox "Error Handler was invoked."
MsgBox CStr(Err) & " " & Err.Description
Resume Exit_ImportClientInput
End Sub

 

[vakjay]

Hi P27br and RobHudson,

Thanks for your replies. P27Br, I will try your solution in future when required. I was able to solve my problem. When your code is in one database and the tables are in a different linked database, you have to create the Import Specication file with the database which contains the code. Still, it can be successfully used to import data in text format (either delimited or fixed length)in to a table in the other database.

Thnaks for your comments again.