Re: using VBA to insert records from access database to sql server

Hi Brendan,

This is Jo again. I modified the code slightly from the previous version by
doing away with the second set of ADODB objects and now I am getting this
error: 'Incorrect syntax near "Provider"'??.

Attached is the code I have written to insert records from an access table
called 'contact' into sql server table called 'contacts'. I have incorporated
your suggestion as well but no luck!

strConn = "Provider=sqloledb;Data Source=ODTESTServer;Initial
Catalog=Pubs;User Id=dummy;Password=abc"

Set oConn = New ADODB.Connection

oConn.Open strConn

oConn.Execute "INSERT INTO contacts (firstname, lastname, email, address,
city, state, zip, phone) VALUES" & _
"('SELECT * FROM contact" & _
"('Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Password=admins;" & _
"User ID=abc1234;" & _
"Data Source=C:\Databases\Contact.mdb;" & _
"Jet OLEDB:System Database=C:\Databases\Security.mdw')');"

Thanks, Jo

"Brendan Reynolds" wrote:

>
> "Jo" <Jo@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> news:A159CFA4-CC76-4BF5-82A7-4E6D6BE490B5@xxxxxxxxxxxxxxxx
>
> > strMDB = "Provider=Microsoft.Jet.OLEDB.4.0; Data
> > Source=C:\Databases\contact.mdb;" & _
> > "Jet OLEDB:System Database=Security.mdw, admins, abc1234"
>
> I'm not sure if it's the only problem here, but the user name and password
> are not part of the System Database section of the connection string, it
> should specify the path/name of the system database only, the user name and
> password are separate. It's probably best to specify the full path/name too.
> I've inserted line-breaks in the example below for illustration purposes
> only ...
>
> Provider=Microsoft.Jet.OLEDB.4.0;
> Password=whatever;
> User ID=whatever;
> Data Source=C:\whatever.mdb;
> Jet OLEDB:System database=C:\whatever.mdw
>
> --
> Brendan Reynolds (MVP)
>
>
>
.

Loading