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

 

[xenou]

Hi, there is no inbuilt solution for this. You'd have to create your own using VBA.
ξ

 

[gdesreu]

Hi, there is no inbuilt solution for this. You'd have to create your own using VBA.
ξ

Yes that is what I wondering if anyone has any insight on. I can get it to work using a static path, but I would it to prompt me for the database path and filename

 

[xenou]

I'm a little confused by your explanation. Are you trying to export a query to another db or run a query that appends data to another db?

 

[gdesreu]

I'm a little confused by your explanation. Are you trying to export a query to another db or run a query that appends data to another db?

Im trying to export data as append to another database however I want to prompt for the database filepath and name to which I exporting to as opposed to a static string.

 

[xenou]

what does your query look like now? Is it just that you are using linked tables so it's a normal append query?

 

[gdesreu]

what does your query look like now? Is it just that you are using linked tables so it's a normal append query?

Typically I use an append query with a linked table but I do not want to have to keep changing the linked table to another database. Basically, I run the same append query to multiple databases that all contain the exact table. I have to send separate databases to different clients so I want to improve the efficiency of that process. Basically run the append query except specify the database I am appending it to with an input box (see bold) It currently static and I need that to be user input. Thanks

INSERT INTO tbl_lab_results ( SAMPID, ANALYTE, RESULT_Lab ) IN [B]'C:\Projects\export\export_test.accdb'[/B]
SELECT tbl_lab_results.SAMPID, tbl_lab_results.ANALYTE, tbl_lab_results.RESULT_Lab
FROM tbl_lab_results;

 

[xenou]

You can write a routine to get the database filepath, then edit the query string and change that part of it. You'll probably have to have a form to make it user-friendly (as much as possible).

 

[gdesreu]

You can write a routine to get the database filepath, then edit the query string and change that part of it. You'll probably have to have a form to make it user-friendly (as much as possible).

Yes its the writing the routine that I am struggling with. In either case I just noticed if you try to handle it through the SQL query the append wont work unless you have the linked table. That means even if I get the query to work, I would still have to link the table in order to get it to work which negates the whole idea. I think I need to handle it all through VBA sub routine. Ill keep plugging away until I get it and post the code. Thanks

 

[xenou]

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.