Inserting records from Excel directly into a SQL database

[Chris]

Hello,

I have very little experience with the more advanced features of Excel and
I'm hoping that some of you can provide me with a little guidance and
suggestions.

What I'd like to do is use macros or VBA (or anything else that might work)
to insert data entries directly into a database (specifically, Interbase).
So, I need to:

1. Populate the columns in my datasheet.
2. For some columns, generate additional information. For example, if I had
three columns for First Name, Last Name, Room number, I would need to
generate the value: First name + Last name + " lives in room " + Room number
+ "." in a fourth column.
3. All four columns of an entry must be entered into a database. This data
may need to be entered into multiple tables so a function call with multiple
insert statements would be necessary.

What's the best approach to do this? Do you have any tips or suggestions on
where I can find more information on how to do this? If you need more
information or clarification, please let me know.

Thanks in advance,

Chris

 

[Chris]

This is true and other solutions have been considered. I guess I should
have mentioned that this application would be an improvement of an existing
process. For the amount of data that we are processing, Excel is the most
efficient form of data entry. Also, we have other workbook sheets that are
already being generated from this master sheet. It would be best to
automate the steps after the initial data entry rather than reengineer the
entire process using a completely different method. This is the reason why
I'm doing some research down this avenue.

 

[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 and add data to it,
* select data from a table,
* delete a table,
* delete a database.

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

The code is open and commented.
--

Regards
Andy Wiggins
www.BygSoftware.com
Home of "Save and BackUp",
"The Excel Auditor" and "Byg Tools for VBA"

 

[Chris]

Thanks Tom, I'll give it a go and see what I come up with.

Cheers,

Chris