Creating and Appending to Access dB using VBA
 

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

Creating and Appending to Access dB using VBA
 

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.


--
Andy Wiggins FCCA
www.BygSoftware.com
Excel, Access and VBA Consultancy
-

wrote in message
oups.com...
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