Hi there,
I have about 20 SQL statements in MS Access 2003 that appends data from one central database to 10 different end-user databases on timer commands. They look a little something like this:

DoCmd.RunSQL "INSERT INTO tblKPI3Results ( EstLabHrsReqd )SELECT [qryKPI3Results].EstLabHrsReqd IN 'K:\Data\AMT\Reporting\AMT Reporting Database.mdb' FROM [qryKPI3Results];"

My question is, can I change the target database by using the value of a field on a form? Im my mind it would have looked something like the following:

(Where Text1 = "K:\Data\AMT\Reporting\AMT Reporting Database.mdb" on the form)

Dim DBLocation as String
Set DBLocation = Me.Text1

DoCmd.RunSQL "INSERT INTO tblKPI3Results ( EstLabHrsReqd )SELECT [qryKPI3Results].EstLabHrsReqd IN BDLocation FROM [qryKPI3Results];"


Can this be done, or does the SQL string have to remain as a text string?

Any help appreciated

Yes, this can bo done. Yes, the SQL string has to remain a string.

Do this:

Create a combo with a list of your target databases. Then do this with your code:

Dim sTarget As String
Dim sSQL As String

If IsNull(Me.cboTarget) or Me.cboTarget = "" Then
   MsgBox "Please select a target database", vbCritical
   Exit Sub
Else
   sTarget = Me.cboTarget
End If

sSQL = "INSERT INTO tblKPI3Results ( EstLabHrsReqd )SELECT [qryKPI3Results].EstLabHrsReqd IN '" & sTarget & "' FROM [qryKPI3Results];"

DoCmd.RunSQL sSQL

Did that help? It is not a good idea to add non-variables to a SQL string at runtime, that is why you post that cbo value to a string, then concatenate the string into your SQL string, then run your SQL string once it is built. Access has a big problem with this:

DoCmd.RunSQL "INSERT INTO tblKPI3Results ( EstLabHrsReqd )SELECT [qryKPI3Results].EstLabHrsReqd IN '" & Me.cboTarget & "' FROM [qryKPI3Results];"



mmcdonal

Look it up at: http://wrox.books24x7.com

Thanks mate. It worked sweet.

I owe you a beer next time you are in town.