How do I load the elements of an array into a database using the SQL insert statement?? Any help appreciated.
TNN, TOM
TNPAYROLL@AOL.COM
TOM
TNN, TOM
TNPAYROLL@AOL.COM
TOM
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
ELEMENTS OF AN ARRAY INTO A DATABASE 1
- Thread starter TNN
- Start date
-
1
- #2
You'll have to use a loop to iterate over the elements of the array and insert one row at a time. You could either use a DAO recordset to do it, or build a string containing an SQL INSERT statement and then use RunSQL within the loop to execute it.
A skeleton using a DAO recordset:
Dim rst As Recordset, i As Integer
Set rst = CurrentDb.OpenRecordset("Table1"
For i = LBound(array) To UBound(array)
rst.AddNew
rst!Field1 = array(i)
rst.Update
Next i
rst.Close
Set rst = Nothing
A skeleton using the RunSQL method:
Const SQLTemplate = "INSERT INTO Table1 (Field1) VALUES("
Dim sql As String, i As Integer
For i = LBound(array) To UBound(array)
sql = SQLTemplate & CStr(Field1) & ""
DoCmd.RunSQL sql
Next i
The recordset method is probably a little more efficient, because the RunSQL method has to open and close the table each time.
Rick Sprague
A skeleton using a DAO recordset:
Dim rst As Recordset, i As Integer
Set rst = CurrentDb.OpenRecordset("Table1"
For i = LBound(array) To UBound(array)
rst.AddNew
rst!Field1 = array(i)
rst.Update
Next i
rst.Close
Set rst = Nothing
A skeleton using the RunSQL method:
Const SQLTemplate = "INSERT INTO Table1 (Field1) VALUES("
Dim sql As String, i As Integer
For i = LBound(array) To UBound(array)
sql = SQLTemplate & CStr(Field1) & "
;"DoCmd.RunSQL sql
Next i
The recordset method is probably a little more efficient, because the RunSQL method has to open and close the table each time.
Rick Sprague
- Thread starter
- #3
Rick,
Not familiar with the RunSQL method. With that code would not the fields have to be string values?? Would that not mean I would have convert all array elements into strings??
What is that DoCmd?? Could that be a command object created in ADO or DAO?? Is my ignorence showing??
Was trying to stay away from the recordset methods as believed they were slower that using SQL---True??
Thank you for your input.
TNN, TOM
TNPAYROLL@AOL.COM
TOM
Not familiar with the RunSQL method. With that code would not the fields have to be string values?? Would that not mean I would have convert all array elements into strings??
What is that DoCmd?? Could that be a command object created in ADO or DAO?? Is my ignorence showing??
Was trying to stay away from the recordset methods as believed they were slower that using SQL---True??
Thank you for your input.
TNN, TOM
TNPAYROLL@AOL.COM
TOM
The RunSQL method would require turning everything into a string, yes, because SQL statements are always passed to Jet as strings. But that doesn't mean that they would be stored in the database as Text values, nor would you have to change them in your array. You need to understand that the string you build for RunSQL is a VBA string, but once it gets passed to Jet, it will be reinterpreted. For example, a SQL statement that inserts an integer value would be built as a string like this:
INSERT INTO MyTable (OrderID) VALUES (12345)
Although the "12345" is part of a VBA string, within the SQL statement it represents a numeric constant value. On the other hand, a SQL statement that inserts a text value would be built in VBA like this:
INSERT INTO MyTable (OrderID) VALUES ('12345')
Do you see the difference?
Since you're having some confusion about this, let me explain how you'd need to build the VALUES entry for each kind of field.
For a String value, you need to enclose it in apostrophes:
"'" & arrayvalue(i) & "'"
For a numeric value, you don't need any delimiters:
CStr(arrayvalue(i))
For a date value, you need to enclose it in "#" characters:
"#" & arrayvalue(i) & "#"
The parenthesized item following the table name is a list of fields you are providing data for, separated by commas. The parenthesized item following VALUES is a list of values, corresponding one-for-one with the list of field names.
If you still don't understand (it's kind of a tough concept), give me a list of field names you're inserting, their data types, and the name and dimensions of the array, and I'll try to write the code for you. It might be easier to understand after that.
It's surprising that you don't know the DoCmd object. You must not have been doing Access coding for long. DoCmd is an Access-provided object that provides very many functions. You should look it up in the Help file, for a start, then examine the Northwind database to see how it's used.
As a general rule, you're right, recordset processing is slower than SQL statements. But that's much less true here, where you're only inserting one record per statement. It's much truer when you're using an Update query, for example, to apply the same updating criteria to all records in a table. Doing that with a DAO recordset is much slower (though the difference will be invisible to the user for small numbers of records).
However, if your array is very large, so that you are adding very many records to the database from it, using either a recordset or SQL will be slow (since you're only inserting one record per SQL statement). You can improve that a little by using a "transaction". A transaction makes a set of updates an all-or-nothing proposition; if a failure occurs while the transaction is active, all updates to that point are "rolled back"--removed from the database. Transactions are more efficient because Jet doesn't have to finalize any updates until after all of them have been performed.
To use a transaction, simply execute this statement before you begin updating:
DBEngine(0).BeginTrans
After you're finished updating, execute this statement:
DBEngine(0).CommitTrans
If a runtime error occurs or something else makes you want to roll back all your updates, you execute this statement instead of the CommitTrans one:
DBEngine(0).Rollback
Rick Sprague
INSERT INTO MyTable (OrderID) VALUES (12345)
Although the "12345" is part of a VBA string, within the SQL statement it represents a numeric constant value. On the other hand, a SQL statement that inserts a text value would be built in VBA like this:
INSERT INTO MyTable (OrderID) VALUES ('12345')
Do you see the difference?
Since you're having some confusion about this, let me explain how you'd need to build the VALUES entry for each kind of field.
For a String value, you need to enclose it in apostrophes:
"'" & arrayvalue(i) & "'"
For a numeric value, you don't need any delimiters:
CStr(arrayvalue(i))
For a date value, you need to enclose it in "#" characters:
"#" & arrayvalue(i) & "#"
The parenthesized item following the table name is a list of fields you are providing data for, separated by commas. The parenthesized item following VALUES is a list of values, corresponding one-for-one with the list of field names.
If you still don't understand (it's kind of a tough concept), give me a list of field names you're inserting, their data types, and the name and dimensions of the array, and I'll try to write the code for you. It might be easier to understand after that.
It's surprising that you don't know the DoCmd object. You must not have been doing Access coding for long. DoCmd is an Access-provided object that provides very many functions. You should look it up in the Help file, for a start, then examine the Northwind database to see how it's used.
As a general rule, you're right, recordset processing is slower than SQL statements. But that's much less true here, where you're only inserting one record per statement. It's much truer when you're using an Update query, for example, to apply the same updating criteria to all records in a table. Doing that with a DAO recordset is much slower (though the difference will be invisible to the user for small numbers of records).
However, if your array is very large, so that you are adding very many records to the database from it, using either a recordset or SQL will be slow (since you're only inserting one record per SQL statement). You can improve that a little by using a "transaction". A transaction makes a set of updates an all-or-nothing proposition; if a failure occurs while the transaction is active, all updates to that point are "rolled back"--removed from the database. Transactions are more efficient because Jet doesn't have to finalize any updates until after all of them have been performed.
To use a transaction, simply execute this statement before you begin updating:
DBEngine(0).BeginTrans
After you're finished updating, execute this statement:
DBEngine(0).CommitTrans
If a runtime error occurs or something else makes you want to roll back all your updates, you execute this statement instead of the CommitTrans one:
DBEngine(0).Rollback
Rick Sprague
- Thread starter
- #5
Rick,
You gave me a lot to chew on. I think I understand most of it. Let me try to put it into practice.
Using a transaction sounds good as I will be inserting 20 records with 60 fields each, all at one time. The array I will be inserting from is 2 dimensioned and dimmed as currency. If I understand you correctly I can use the array assignments in my SQL statement treating them as numerics without any delimiters.
Yes, I am relatively new to SQL. Have never used transactions before. Will have to research. Sounds like If I use a transaction there would be no need for a FOR loop. Do I write an INSERT statement for each record and somehow string them together to make a transaction???
I am using ADO and the cmd.Execute statement to execute my cmd.CommandText = "SQL"
Don't believe I will have any troubl writing the INSERT statements but creating and managing a transaction may cause me a problem.
Don't want to bother you writing code. I'll give it a try and if I get stuck I'll yell.
Thank's Rick. I have printed all your comments and will go over them.
TNN, TOM
TNPAYROLL@AOL.COM
TOM
You gave me a lot to chew on. I think I understand most of it. Let me try to put it into practice.
Using a transaction sounds good as I will be inserting 20 records with 60 fields each, all at one time. The array I will be inserting from is 2 dimensioned and dimmed as currency. If I understand you correctly I can use the array assignments in my SQL statement treating them as numerics without any delimiters.
Yes, I am relatively new to SQL. Have never used transactions before. Will have to research. Sounds like If I use a transaction there would be no need for a FOR loop. Do I write an INSERT statement for each record and somehow string them together to make a transaction???
I am using ADO and the cmd.Execute statement to execute my cmd.CommandText = "SQL"
Don't believe I will have any troubl writing the INSERT statements but creating and managing a transaction may cause me a problem.
Don't want to bother you writing code. I'll give it a try and if I get stuck I'll yell.
Thank's Rick. I have printed all your comments and will go over them.
TNN, TOM
TNPAYROLL@AOL.COM
TOM
Tom,
No, I'm afraid you'll still need the For loop. As far as I know, there's no way to insert multiple rows with a single SQL statement. To implement the transaction, you'll bracket your For...Next loop with BeginTrans...CommitTrans, as in this example:
I have yet to get my feet wet in ADO. I'm afraid I was assuming you were talking about DAO recordsets (ADO is new with Access 2000). Now I'm not so sure my comments are as sound, so keep an open mind. Rick Sprague
No, I'm afraid you'll still need the For loop. As far as I know, there's no way to insert multiple rows with a single SQL statement. To implement the transaction, you'll bracket your For...Next loop with BeginTrans...CommitTrans, as in this example:
Code:
Set rst = db.OpenRecordset(...)
DBEngine(0).BeginTrans
For i = 0 To 19
rst.AddNew
For j = 0 To 59
rst.Fields("Field" & CStr(j)) = MyArray(i, j)
Next j
rst.Update
Next i
DBEngine(0).CommitTrans
rst.Close
Set rst = Nothing- Thread starter
- #7
Thanks again, Rick
Look back now and then. If your interested I'll let you know whats happening.
Thank You,
TNN, TOM
TNPAYROLL@AOL.COM
TOM
Look back now and then. If your interested I'll let you know whats happening.
Thank You,
TNN, TOM
TNPAYROLL@AOL.COM
TOM
- Status
Similar threads
- Locked
- Question
- Locked
- Question
