OELDB Query

[GlenLynam]

Afternoon,
I am trying to complete an app i have been writing if you can assist please do. Im creating an mde that will connect to another database and auto import the data into it via a csv.

I have created the cobbection as follows and this appears to work.

Private Sub Form_Load()

Dim conn As ADODB.Connection
Set conn = New ADODB.Connection

With conn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "data source=C:\calllogger2002.mdb"
.Open
End With

End Sub

The problem im gettin is that i cant get my code to do anything on the other access database.This is the code i am using (it does work if i use it in he actual database)

DoCmd.SetWarnings False
DoCmd.OpenQuery "DeleteImportTableContents"
DoCmd.TransferText acImportDelim, , "IMPORT", "C:\Program Files\Avaya\IP office\CCC\DeltaServer\SMDR_Output\smdr.csv", True
DoCmd.OpenQuery "UpdateSMDRFromImport"
DoCmd.DeleteObject acTable, "SMDR_ImportErrors"
DoCmd.SetWarnings True
DoCmd.Quit

Basically i want to execute this code on the original database from a completely seperate MDE file.

Thanks

Glen

 

[HitechUser]

Glen,

I am not quire sure what you are trying to accomplish, but here might be an alternative. This can be done without any additional code. Consult the help taxt on how to delete the link when your application as finished. Try using a simple link as follows ...

DoCmd.TransferDatabase acLink, "Microsoft Access", "C:\DBS\NWSales.mdb", acTable, "tblIMPORT", "tblIMPORT"

This will create a link to your table in the your MDB file. Your queries, forms etc. will look at this table as if it were a local table.

NOTE: If you have a table called "tblIMPORT" in the local database then a link cannot be created. Access treats the linked table as local (name must be unique.) Rename your local table to something like "zIMPORT" then create your link.

Your query should run just fine.

A quick test... Use the link table from the File.. Import menu. Create your link and run your test.

Good Luck

 

[HitechUser]

Glen,

Your can quickly delete your link by doing the follwing.

DoCmd.DeleteObject acTable, tblIMPORT

Sooo maybe this.

DoCmd.SetWarnings False
DoCmd.TransferDatabase acLink, "Microsoft Access", "C:\DBS\NWSales.mdb", acTable, "IMPORT", "IMPORT"
DoCmd.OpenQuery "DeleteImportTableContents"
DoCmd.TransferText acImportDelim, , "IMPORT", "C:\Program Files\Avaya\IP office\CCC\DeltaServer\SMDR_Output\smdr.csv", True
DoCmd.OpenQuery "UpdateSMDRFromImport"
DoCmd.DeleteObject acTable, "SMDR_ImportErrors"
DoCmd.DeleteObject acTable, IMPORT
DoCmd.SetWarnings True
DoCmd.Quit