Run Query Accross multiple DB's?

[Leather]

Is it possible to run an update accross 2 databases?

I need to open a text file using the MS Text driver, and run an update to an access database with the contents of the text file...

NOTE: I can not open the text file in access or anything like that as this will run on a client machine which may not have access installed.. (just the jet drivers).

[Leather]

ANYBODY???


^BUMP^

[Jerry Grant]

This should be possible, use something like the following:

Public Sub P_CopyTable( _
                       ByVal strSourceDb As String, _
                       ByVal strDestinationDb As String, _
                       ByVal strTable As String _
                       )
 
    Dim strSQL          As String
    Dim wrkJet          As Workspace
    Dim dbSource   As Database
    On Error GoTo AppendErr
 
    ' Open Microsoft Jet workspace & Microsoft Jet database.
    Set wrkJet = CreateWorkspace("", "admin", "", dbUseJet)
    Set dbSource = wrkJet.OpenDatabase(strSourceDb, , ,"Text")
 
    
    strSQL = "SELECT [" & strTable & "].* " & _
             "INTO [" & strTable & "] " & _
             "IN '" & strDestinationDb & "' " & _
             "FROM [" & strTable & "]"
             
    frmUdate.lblStatus(1).Caption = "Copying existing data from " & strTable
    DoEvents
    
    dbSource.Execute strSQL
    
    dbSource.Close
    wrkJet.Close
    
    Exit Sub
    
AppendErr:
    If Err = 3078 Then Resume Next
 
End Sub

TheThis is using DAO, but you should be able to be done with ADO with a few changes.

[Jerry Grant]

Cough!

Error correction...

Use dbUseODBC instead of dbUseJet and ensure your Text file has a DSN...

[Leather]

Thanks..

But what are you apssinf as teh detsination db?? a DSN name, or a connectionstsring? or a file name?

[Leather]

Please jerry! : )

[Jerry Grant]

If But what are you apssinf as teh detsination db??
Means But what are you passing as the destination db??

A: The Path to the destination database

I use routine as part of an application to convert a legacy Access 97 database with a bad schema design into a new Access 2000 database with a normalized schema.

So in theory, it should work with a Text file as the source.

Check the MSDN for exact syntax for these two formats.

[Leather]

Thanks,

Sorry for the multiple typos...

Wasnt concentrating.

I got it working a treat!