Passing Variable Values from Current db to External Database

[Ross]

Hi
How can I pass an sql string with values from the current database to an
external database and then run that query in the external database. I almost
have it but am stuck.

'The SQL string
'The variables
SQL = "UPDATE Links SET Links.CUser = """ & strTO & """ "
SQL = SQL & "WHERE (((Links.CUser)=""" & strFrom & """));"

Get_File_Info

'This correctly opens the external database
Dim AppAccess As New Access.Application
With AppAccess
.OpenCurrentDatabase (strPathFile)

' This code runs but the criteria in the SQL string don't seem to carry into
the 'external Database

DoCmd.RunSQL SQL
.CloseCurrentDatabase
End With

Thank you

Ross

 

[Tom van Stiphout]

On Fri, 5 Feb 2010 05:33:02 -0800, Ross

The sqlstatement looks OK. Set a breakpoint to inspect the exact
value, then paste it in a new query in the external db to see if it in
fact works.

Also, shouldn't that be:
..DoCmd.RunSQL SQL

-Tom.
Microsoft Access MVP

 

[GeoffG]

I would use DAO, not Automation.
With DAO, you don't need to start another copy of Access.
I think that closing a second copy of Access can sometimes
be problematic.

Here's how I would do it:

' This module requires a reference to the
' Microsoft DAO Object Library
' (In the VBA editor, Tools > References)

Private Sub UpdateExternalDatabase( _
strDBFullPath As String, _
strSQL As String)

Dim objWS As DAO.Workspace
Dim objDB As DAO.Database

On Error GoTo Error_UpdateExternalDatabase
Set objWS = DBEngine.Workspaces(0)
Set objDB = objWS.OpenDatabase(strDBFullPath)
With objDB
.Execute strSQL, dbFailOnError
MsgBox "Records updated: " & .RecordsAffected
End With

Exit_UpdateExternalDatabase:

On Error GoTo Abort_CleanUp_UpdateExternalDatabase
If Not objDB Is Nothing Then
objDB.Close
Set objDB = Nothing
End If
Set objWS = Nothing
Exit Sub

Abort_CleanUp_UpdateExternalDatabase:

Exit Sub

Error_UpdateExternalDatabase:

MsgBox "Error No: " & Err.Number _
& vbNewLine _
& "Error Description:" & vbNewLine _
& Err.Description
Resume Exit_UpdateExternalDatabase

End Sub


Geoff

 

[Ross]

GeoffG,

I plugged you code in and it works perfectly!

Many Thanks

Ross