Re: using VBA to insert records from access database to sql server
- From: "Brendan Reynolds" <anonymous at discussions dot microsoft dot com>
- Date: Tue, 19 Apr 2005 19:06:07 +0100
I don't think you can specify a connection string *within* a query like
that, Jo.
My preferred solution to this kind of problem would be to use ODBC to link
the Access MDB and the SQL Server table. It would then be a simple matter to
write an append query to append the records from the local Jet table to the
linked SQL Server table. My second preference would be to open two
recordsets, one on the local table and one on the SQL Server table, and loop
through the first adding each record to the second.
Before we go any further, though, we ought to clarify something - where is
this code running? If it is running within the Access MDB that contains the
data (or is linked to the data), all of this Jet connection string stuff is
redundant - we can use CurrentProject.Connection for that. We only need to
create a new connection to the SQL Server data.
--
Brendan Reynolds (MVP)
"Jo" <Jo@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:E8B5189E-5D4F-4DA1-A022-70997D6A5C7C@xxxxxxxxxxxxxxxx
> 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)
>>
>>
>>
.
- Follow-Ups:
- References:
- Prev by Date: Re: Why is the Fomat function not supported in Access 2003?
- Next by Date: On Insert or Update - index out of bound error
- Previous by thread: Re: using VBA to insert records from access database to sql server
- Next by thread: Re: using VBA to insert records from access database to sql server
- Index(es):
Relevant Pages
|
