Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel appending to Access - no duplicates | Excel Discussion (Misc queries) | |||
Creating an access log | Excel Programming | |||
Appending to Access table | Excel Programming | |||
Appending to Access table | Excel Programming | |||
Appending Excel file to an Access table | Excel Programming |