View Full Version : Insert record from backup database

Gary Williams
07-05-2004, 04:59 PM
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?



07-05-2004, 09:17 PM

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
07-05-2004, 09:49 PM
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. :)



07-06-2004, 12:23 PM
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
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
fieldValues = delimiter & field.value & delimiter
end if

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.


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.

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


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