dynamically creating a SQL Server insert statement

[jmev7]

Does anyone have a script to auto populate SQL server tables from an Access
table? The table types match, as the Access file was created from a SQL
server export to Access, and the target SQL server database is identical to
the original.

The goal is to step through each field, determine the data type and write
the SQL server insert statement with the data from the current Access record
with appropriate field delimiters and any other required syntax. For
example, if the row in a given table contains the following:

ID: AutoNumber
Contractor: Text(50)
Address: Text(150)
City: Text(50)
State: Text(2)
Zip: Text(10)
DateAdded: Date/Time


The idea is to read create an insert statement for the matching SQL Server
table something like this:

"Insert into tblContractor (ID, Contractor, Address, City, State, Zip, )
Values ('{8455A759-A25B-4E02-840A-000A8054FC86}', 'O'Neil Construction
Services', '123 Main St.', 'Lake City', 'FL', '32111', '12/1/06')

I don't know if this is even all correct, but I expect it should work for
most records. I know I can use trial and error, but I don't know if I'll get
all of the fields or data types required so I can hand this over to someone
else with the claim that it will always work. One of the problems I've
already experienced is that record with values like {O'Neil} in a varchar
field, or even double quotes as is often the case, will botch up any
automation. Can anyone suggest a fix for that as well?

Thanks for any feed back.

 

[Albert D. Kallal]

I guess I have to ask the silly question?

Why not bind the form to the sql server table, and then NO code need be
written?

Just use a linked table. I don't see the reason/need to start writing a
large amount of code to build out a sql update string.

if you bind the form to the linked sql server table, then ultimately,
ms-access under the hood will actually build that sql update for you!