Executing multiple SQL statements in Access 1

[aNewbie]

I am trying to execute multiple INSERT SQL statements in a single MS Access query. It appears that MS Access allows only one SQL statement within a query. This is a limitation not found in other MS database programs like Foxpro. Any suggestions on batching multiple SQL statements in Access?

Thanks in advance.

 

[cmmrfrds]

Assuming an ADO connection.
Dim cn as New ADODB.Connection
Set cn = currentproject.connection

cn.Execute "insert etc...;insert etc...;insert etc..."


String the statements together with semi colons.

 

[aNewbie]

Thanks for the quick response. I am a newbie to MS Access, where is the best place execute this code from?

Gene

 

[cmmrfrds]

There is really no best place, but where do you need it. You can always put code behind the Click event of a Button, but basically any event on a Form, or in a Macro, etc.....

 

[aNewbie]

Characters found after end of SQL statement." is the run-time error I got when I tried your code. Your code, however, does work for a singe SQL statement.

The reason for this exercise is to programmatically transfer data from Excel into Access. I have successfully concatenated row information from Excel into a series of "INSERT" or other SQL statements then invoke MS Foxpro via VBA to execute these SQL statements. This technique would not work in Access if it can not batch multiple SQL statements.

I appreciate any thoughts on transferring data from Excel to Access.

 

[cmmrfrds]

The simplest way to transfer Excel data to an Access table is to link the Excel Spreadsheet in Access and then write a Select INTO or Insert INTO SQL Statement. You could also import the spreadsheet and do the same thing. Once, the Excel spreadsheet is in Access either by Link or Import then you can write any kind of SQL you need to do the update.

 

[aNewbie]

Thanks for the insights.