How to execute SQL in automation

[elwi]

I'm opening a second database (db2).
Copying the structure of a db2 table to db3.
Populating the table in db3 with a subset of records from db2.

I've gotten as far as opening the db2 and copying the table structure. Can't figure out how to run the query using execute rather than docmd.openquery.

Dim appAccess As Access.Application
Set appAccess = New Access.Application
appAccess .OpenCurrentDatabase "DbPathString"
 
'copy the table structure to dbQn, overwrites any previous with same name
appAccess.DoCmd.TransferDatabase acExport, "Microsoft Access", DestinationDb.Name, acTable, SourceTableName, DestinationTableName, True
 
'populate the table in db3
strSql = "INSERT INTO ......."
           " IN '" FullDbPathString "'[MS ACCESS;];"
 
'how to execute SQL?
??.Execute strSql
 
appAccess.CloseCurrentDatabase
appAccess.Quit
Set appAccess = Nothing

Any helpful hits appreciated.

 

[madefemere]

Hello,

It's depending where is your SQL code. You can execute a query with :

Docmd.RunCommand strSQL

or with the name of the database with

Dim db as DataBase
Set db = yourdb
db.Execute strSQL

Good continuation

 

[elwi]

Appreciate your assist.

I decided not to execute the query in the automation code. Worked better to run a procedure in the remote database that performed several operations including executing the procedure.

 

[Galaxiom]

You can execute a query with :

Docmd.RunCommand strSQL

Incorrect. RunCommand is used to run menu and toolbar commands. The argument does not accept a String but rather a Long that represents the command to be run.

You are thinking of

DoCmd.RunSQL strSQL

or with the name of the database with

Dim db as DataBase
Set db = yourdb
db.Execute strSQL

Incorrect again. Supplying the name of the database will not work in that context.

A pointer to the current database is set by using the CurrentDb method of the Application. If the execution is to be done in another database the OpenDatabase Method is used.

When using Execute the second argument should also be included. Otherwise any errors with the execution will be silently ignored.

Currentdb.Execute strSQL, dbFailOnError

Either method can only run action queries. The two methods have important differences.

Being a method of the database object, Execute has no comprehension of Access objects such as forms. Consequently any values from forms must be concatenated into the string along with the appropriate delimiters.

RunSQL interprets Access objects so can include references to controls on forms in the SQL string. However it also raises the same warnings as running a query manually unless warnings are turned off. However this also results in errors being silently ignored. Warnings will stay disabled until turned back on so it must be used very carefully.

Generally Execute is the preferred method.

 

[arnelgp]

Copying the structure of a db2 table to db3.
Populating the table in db3 with a subset of records from db2.

if your main goal is to copy a subset of your table from database db2 to your new database, db3, then the code is very simple:
open your old db2 and execute this from a module:

Private Sub TestCopy()
    Dim dbs As DAO.Database

    Set dbs = CurrentDb

    dbs.Execute "SELECT * INTO " _
        & "[NewTable] IN 'z:\db3.accdb' FROM [yourTableIn_db2]"
        
    
    Set dbs = Nothing

End Sub

you should supply the correct path to your db3.
if you want same table name to exist in db3, put the "NewTable" should be the same name as in "yourTableIn_db2".

supply the correct filter to copy only records that meet your condition:

...
...
    dbs.Execute "SELECT * INTO " _
        & "[NewTable] IN 'z:\db3.accdb' FROM [yourTableIn_db2] WHERE ..."