I have a SQL 2005 database called HWG with a table in it called Contacts. There are a number of columns but I am only interested in FirstName, LastName, and Workemail all nvarchar.
I also have a Access database called “Copy of Main Client DB” (note spaces in name) with a table in it called emails. There are several columns, but I am only interested in “Last Name”, “First Name”, and EmailAddress (note spaces in the column names)
For the price of a few cigars, I need you to help me with the following task - I need to insert a row in the SQL database filling only the FirstName, LastName, and Workemail columns. The data will come from the Access database “Last Name”, “First Name”, and EmailAddress columns.
I have both databases on each of two computers (trying different solutions) and have tried the following.
On one computer (32 bit XP) I set up an ODBC link from the Access to the SQL database and can perform a select * from the Access DB looking into the SQL db. I am not able to do the insert into the SQL db I get the infamous “syntax error” (That is Microsoft for “you made a mistake, stupid, and if you are that dumb I am not going to help you by giving you any more information.)
On the other computer, 64 bit w7) I have, from within SQL 2005, created a linked server (using the SQL Below) called HWG. I do not know how to reach into the access database and pull the data for the insert.
sp_addlinkedserver 'HWG', 'Access 2007', 'Microsoft.Jet.OLEDB.4.0',
'c:\Macs Stuff\Copy of Main Client DB.mdb'
(I think I have discoverd that Microsoft.Jet.OLEDB.4.0 will not run on a 64 bit machine)
In either case, How do I write a SQL that that will cause a new row to be created in the SQL DB from the Access DB?