Insert record from backup database

[Gary Williams]

Hi All,

I have two .mdb databases on my web server. The backup holds all the records and the main database holds only the records for the last 6 weeks. I need to extract one, single record from the backup and insert it into the main database.

I know how to insert a record using:

INSERT INTO table_name (column1, column2,...)VALUES (value1, value2,....)

but how do I extract the record from the backup in this format?

Regards

Gary

[Roelf]

uhm....

read the values, type the insert command by hand, execute the query???

Doesn't access hase some thingy to export a record to a text file? Use that to build the insert statement...

[Gary Williams]

Hi Roelf,

Sure, it can be done by hand but I have about 60 fields. I was just hoping there was a simple way to get the data into the insert statement. :)

Regards

Gary

[glenngv]

Try this:

dim fieldNames, fieldValues, delimiter

'open connection to backup db

'retrieve desired record from backup db

for each field in recordset1.fields
if fieldNames<>"" then
fieldNames = "," & field.name
else
fieldNames = field.name
end if
select case vartype(field.value)
case 1 'null
case 2 'integer
delimiter = ""
case 8 'string
delimiter = "'"
'add additional cases for other types
case else
delimiter = ""
end select
if fieldValues<>"" then
fieldValues = "," & delimiter & field.value & delimiter
else
fieldValues = delimiter & field.value & delimiter
end if
next

strSQL = "INSERT INTO table_name (" & fieldNames & ") VALUES (" & fieldValues & ")"

'open connection to target db

'execute strSQL

The code only covers string and numeric fields, add as necessary. Here is the reference for the different vartypes.

http://www.devguru.com/Technologies/vbscript/quickref/vartype.html

I'm not sure if the VarType technique is applicable for ADO Field's datatype. If that doesn't work, try ADO's Field.Type (http://www.devguru.com/Technologies/ado/quickref/field_type.html) property.
Change:

select case vartype(field.value)
'cases for different vartypes here
end select

to:

select case field.type
'cases for different field.type here
end select