exporting append queries with database filename prompt

[gdesreu]

In my main database I execute an append query which I point to an external database table. The append query never changes and neither does the table name in the other database that I am appending it to. The issue I would like to solve is: right now the database filename is stored at the time the append query is saved, so as I make a new database for another client I have to change the database name and relink to the new table. If I export a select query it simply overwrites the table and I cant have that, I need to append the data to the table in the other database.

Is there a way to run an append query using VBA that simply prompts for the database filename location.
In other words export an append query to a database by prompting me for the location.
Thanks

 

[gdesreu]

You shouldn't need linked tables for this to work with an external query.

Here's a sample (not the most robust but should work fine):

Sub bar()

Dim qdf As QueryDef
Dim s As String
Dim i As Long, j As Long

    Set qdf = CurrentDb.QueryDefs("Query7")
    i = InStr(UCase(qdf.SQL), "IN ")
    j = InStr(UCase(qdf.SQL), "SELECT ")
    s = Replace(s, Mid(s, i + 4, j - i - 7), "C:\newfolder\newdb.accdb")
    
    DoCmd.SetWarnings False
    DoCmd.RunSQL s
    DoCmd.SetWarnings True

    Debug.Print i
    Debug.Print j
    Debug.Print "|" & Mid(s, i + 4, j - i - 7) & "|"
    Debug.Print s

End Sub

It would be easiest if your query was never altered and never actually used. Then you could write it as:
INSERT INTO ... IN XXX ...
and your code just replaces XXX and you are good to go no worries.

This fails at the runSQL: Runtime error 3129 Invalid SQL statement expected delete, Insert, Procedure, Select or Update. And yes, the query wont change nor does the destination table, the only thing that changes is name and filepath of the destination database. I am looking for a input box to change the name of the destination database. Typically this is handled in the append query setup. If I have a hundred different databases I need to either make a hundred append queries or modify the query every time I use it to retype the new path.

 

[xenou]

Post your sql statement (the same one that results from the code - that is the one that is getting the error). You might want to post your actual code as well.

 

[gdesreu]

I was able to come up with a solution that was what I was looking for. This simply replaces my Append query SQL with a user defined filepath/name that is derived from a form textbox. Thanks for the help Xenou but I think Im all set now.

Private Sub Command72_Click()
On Error GoTo Error_Handler
Dim sQryName As String
Dim sSQL As String
Dim qdf As DAO.QueryDef
Dim destination As String
Dim dest1 As String
Dim dest2 As String
Dim dest3 As String

destination = "'" & ([Text73].Value) & "'" ' user input for filepath, currently derived from text box on userform

   Set qdf = CurrentDb.QueryDefs("qry_export_append") ' append qyuery to be exported
   dest1 = "INSERT INTO tbl_results IN " 'SQL statement assembly1
   destination = ([Text73].Value) ' sql statement assembly2
   dest2 = " SELECT tbl_results.* FROM tbl_results;" 'sql statement assembly3
   dest3 = dest1 & "'" & destination & "'" & dest2 ' final sql
   sSQL = dest3 ' sql to insert
     MsgBox "SQL query will be modified to:" & vbCrLf & dest3
    'Redefine the Query's SQL
        qdf.SQL = sSQL
Error_Handler_Exit:
    On Error Resume Next
    Set qdf = Nothing
    Exit Sub

Error_Handler:
    MsgBox "MS Access has generated the following error" & vbCrLf & vbCrLf & "Error Number: " & _
    Err.Number & vbCrLf & "Error Source: RedefQry" & vbCrLf & "Error Description: " & _
    Err.Description, vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit

End Sub