Inserting records from Excel directly into a SQL database

  • Thread starter Thread starter Chris
  • Start date Start date
C

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
 
Don't see anything here that would indicate that Excel is necessary. Seems
like you could do this all in your database.
 
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.
 
You can use ADO to update your table. I believe Mr. Erlandsen has sample
code for this:

http://www.erlandsendata.no/english/vba/adodao/

You also might do a google group search in this newgroup for posts by
"onedaywhen" who has posted a lot of ADO code.

http://groups.google.com/advanced_group_search?hl=en

http://support.microsoft.com/?kbid=278973
SAMPLE: ExcelADO Demonstrates How to Use ADO to Read and Write Data in Excel
Workbooks

=A2 & " " & B2 & " Lives in Room " & C2

could be placed in the 4th column and filled down.

set rng = Range(cells(2,1),Cells(rows.count,1).End(xlup))
rng.offset(0,3).formula = "=A2 & "" "" & B2 & "" Lives in Room "" & C2"
rng.offset(0,3).formula = rng.offset(0,3).Value
 
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"
 
Back
Top