Can not pass variable value to insert into statement

[techbuddha]

Hi new to the forum and visual basic.

I am attempting to fix a migration of excel to access. The excel sheets where simple copied as is into access. for example one table lists the academic history from elementary to phd and on to post doctoral work alll on the same row. I want to break that up to have each level of education as a seperate record and then relate that back to the person.

i can pull the data into a recordset
I can iterate through it by row and column and place the data into an array

from there I was going to simple insert the data into a table with new metadata.

One row for each level of education, but I can not pass the array value back to the database. It recognized the variable names literally or as functions i.e. as "edu(0) = function not array variable or undefined as the variable is not surrounded with quotes.

I thougth is should just declair a command object e.g. objcmd as new command,
define the command txt e.g. objcmd.commandtext = "insert into table (field, field) values (edu(0),edu(1))",
and then execute the command e.g. objcmd.execute

but the value is either not defined or is considered a function. It works if I put in literal values like "test" or 1234 ,but not with variables. How do I pass back to the DB. the values I got from it via recordset?

any help is greatly appreciated.

[Killer42]

Hi. Welcome to the forum.

What you need to do is insert the values of the variables into the SQL statement, not their names. With single quotes around them if they are strings, of course.

For example...

objcmd.commandtext = "insert into table (field, field) values " _
  & "('" & edu(0) & "', '" & edu(1) & "')"

[techbuddha]

Hi. Welcome to the forum.

What you need to do is insert the values of the variables into the SQL statement, not their names. With single quotes around them if they are strings, of course.

For example...

objcmd.commandtext = "insert into table (field, field) values " _
  & "('" & edu(0) & "', '" & edu(1) & "')"

Hi thanks for the quick reply :-) One question would the Docmd.runSql give me the same results? given that I make the same changes to the sql statement you mentioned above?

thanks a bunch

[techbuddha]

Hi thanks for the quick reply :-) One question would the Docmd.runSql give me the same results? given that I make the same changes to the sql statement you mentioned above?

thanks a bunch

sorry nevermind I guess I should just try it. Thanks again for the help

[Killer42]

sorry nevermind I guess I should just try it. Thanks again for the help

How did it turn out?

[techbuddha]

works great! thanks alot