Creating and Appending to Access dB using VBA

[dcatario]

Hello, Everyone,

I'm looking for help in a project that writes data gathered from web
sites/text files into an Access database.

What I need to do is:
1. Create a table in Access with the required fields.
2. Write each record into the new table

As for task 1, I would like to automate the creation of the fields. I
can generate the field names with some loops and store them in a string
array, but I'm not sure how to get them into the Access table. There
are a lot of them (a few hundred), so I'd rather not generate them by
hand...

As for task 2, I'd like to append records to the newly-created table.
Is this only possible to do record-by-record, or can I just write all
of my information into the table in one step? Not all of my data is in
one array; while some is, the rest is spread over a few variables. It
is all text.

Also, I have not written my information into a spreadsheet and am
trying to send it to Access directly from the VBA variables - is this
the correct approach to be taking?

I'm using Windows 2000 Professional with Excel 2000 and Access 2000.

Thanks for all of your help!

Dave

 

[Andy Wiggins]

This might be a help for getting data to and from Excel and Access: It
includes examples of using variables in SQL queries.
http://www.bygsoftware.com/examples/sql.html

Or you can get there from the "Excel with Access Databases" section on page:
http://www.bygsoftware.com/examples/examples.htm

It demonstrates how to use SQL in Excel's VBA to:

* create a database,
* create a table
* insert records
* select records,
* update records,
* delete records,
* delete a table,
* delete a database.

DAO and ADO files available.

You can also download the demonstration file called "excelsql.zip".

The code is open and commented.