VBA in Excel to Create Table in Access

[rsj88]

Hi,

I am not able to find a good thread online regarding this.

I am trying to run a VBA script in Excel to run the following statement (Select * Into Table1 From (Select * From Data)

I have tried various scripts and many come across various errors.

The script should connect to the DB and Run the create table query. if the table already exists this should delete it first.

Any ideas please?

Thanks Team!!

 

[bobsan42]

Errors in code are part of the process. The task is to clear some and handle others.
Generally, You must create a connection (using ADODB or DAO) to the db and then use the connection's Execute method to run the SQL statement.
Post a sample code and we can try to get rid of the errors.

 

[rsj88]

yeah ive created the connection which is a ADODE connection.

This is referred to as

Module1.connect

Module 1.conn.execute ("Select * Into Table1 From (Select * From Data)
''Also tried Module 1.conn.execute ("Create_Table1")

Module1.disconnect

Error occurring is invalid SQL statement: expected delete insert procedure Select or update.

 

[bobsan42]

try like this:

Select * Into Table1 From Data

 

[rsj88]

Perfect Thanks. works now!

i had to put in a delete query to delete the table beforehand before we can create it as we are not able to overrate.

 

[bobsan42]

You can use INSERT instead of SELECT INTO if the table exists.