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 MikeeOK 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
tlbroadbent
MIS
What is the source of your data? What program are you using to load the data? Terry
- Thread starter
- #3
Terry,
I am using an access database in VB6.0. The array is a 2 dimensional array(created with the Dim statement) which is filled with data from user input into text boxes. Depending on the type of record that the user inputs to the text boxes, this determines where it is placed in the array.
I need to load the elements of the array into my database.
TNN, TOM
TNPAYROLL@AOL.COM
TOM
I am using an access database in VB6.0. The array is a 2 dimensional array(created with the Dim statement) which is filled with data from user input into text boxes. Depending on the type of record that the user inputs to the text boxes, this determines where it is placed in the array.
I need to load the elements of the array into my database.
TNN, TOM
TNPAYROLL@AOL.COM
TOM
tlbroadbent
MIS
Tom,
Do you need help with the SQL Insert syntax, the VB code or both? Does the database already exist? Tell us about the structure of the table you'll Insert Into. Will you be inserting values into every column?
Terry
Do you need help with the SQL Insert syntax, the VB code or both? Does the database already exist? Tell us about the structure of the table you'll Insert Into. Will you be inserting values into every column?
Terry
- Thread starter
- #5
Terry,
I will be inserting values into every column, approx 60 fields. Access Database already exists. VB code and the insert syntax is no problem but how do I get the array elements into the SQL statement? Do I have to put them into String variables first or what?? Do not want to use recordset methods as believe they are not as fast--True??
Thanks.
TOM, TNN
TNPAYROLL@AOL.COM
TOM
I will be inserting values into every column, approx 60 fields. Access Database already exists. VB code and the insert syntax is no problem but how do I get the array elements into the SQL statement? Do I have to put them into String variables first or what?? Do not want to use recordset methods as believe they are not as fast--True??
Thanks.
TOM, TNN
TNPAYROLL@AOL.COM
TOM
tlbroadbent
MIS
Is this what you need?
Assumptions:
1- The array is X(m,n) and is string data. You can adjust accordingly.
2- The Access table has a Key Column and m X n data columns.
3- The data columns contain text data.
Dim dbs As Database, sql as string
'Open the database
Set dbs = OpenDatabase("MyDB.mdb"
' Create the Inert statement
sql = "Insert Into MyTbl (KeyCol, Col1, Col2,... Col60) "
sql = sql & " Values(KeyData,"
For i = 1 to m
For j = 1 to n
sql = sql + ",'" + X(i,j) + "'"
Next j
Next i
sql=sql & ""
' Execute the insert to add the row
dbs.Execute sql
' Close the database
dbs.Close
Terry
Assumptions:
1- The array is X(m,n) and is string data. You can adjust accordingly.
2- The Access table has a Key Column and m X n data columns.
3- The data columns contain text data.
Dim dbs As Database, sql as string
'Open the database
Set dbs = OpenDatabase("MyDB.mdb"
' Create the Inert statement
sql = "Insert Into MyTbl (KeyCol, Col1, Col2,... Col60) "
sql = sql & " Values(KeyData,"
For i = 1 to m
For j = 1 to n
sql = sql + ",'" + X(i,j) + "'"
Next j
Next i
sql=sql & "
;"' Execute the insert to add the row
dbs.Execute sql
' Close the database
dbs.Close
Terry
- Thread starter
- #7
Terry,
The array is 2 dimensional and dimmed as currency. The Access table has 60 columns(fields) and I want to insert 20 records at a crack from the array to the table.
The data fields contain numbers.
Thank you for the code. Will see if I can use it.
TNN, TOM
TNPAYROLL@AOL.COM
TOM
The array is 2 dimensional and dimmed as currency. The Access table has 60 columns(fields) and I want to insert 20 records at a crack from the array to the table.
The data fields contain numbers.
Thank you for the code. Will see if I can use it.
TNN, TOM
TNPAYROLL@AOL.COM
TOM
- Status
Similar threads
- Locked
- Question