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

Brendan and Tim,

Thank you for your prompt reply, I greatly appreciate it. I stopped checking
for responses as I coudln't see my post. I was able to fix the problem and
guess went with the second preference suggestion you had made!.

I have used the same OLE DB providers and using recordset object to query
Access table and walk through the recordset to get the values. I am able to
insert records from Access table to SQL server table with one error which I
have mentioned below.

Your question about where the code is running: The code runs within Access
and your point absolutely makes sense but it somehow didn't work the first
time I tried using it so...

This is the working code:

--------------------------------begin
code----------------------------------------------------

''----------these objects are for inserting data to sql server
Dim oConn As ADODB.Connection
Dim oRS As ADODB.Recordset
Dim oComm As ADODB.Command

''-------this second set of adodb objects are for querying the access
database's contract awards database
Dim objConn As New ADODB.Connection
Dim objRS As New ADODB.Recordset

Dim strConn As String
Dim strInsert As String
Dim strSelect As String
Dim strMDB As String

''----------connection string to connect to SQL Server through OLE DB
provider -------------
strConn = "Provider=sqloledb;Data Source=ODTESTServer;Initial
Catalog=Pubs;User Id=dummy;Password=abc"

''---------connection string to get records from access database, only users
defined in the workgroup file have access to carry out this functionality

strMDB = "Provider=Microsoft.Jet.OLEDB.4.0;Password=abc1234;User ID=admins;"
& _
"Data Source=C:\Databases\Contact.mdb;Jet OLEDB:System
Database=C:\Databases\Security.mdw"

''---------SELECT QUERY and walk through the recordset of access database to
insert values into SQL server's contacts table ---------------

strSelect = "SELECT * FROM Contacts"

''***************initialize access objects **********************
objConn.Open strMDB
Set objRS = objConn.Execute(strSelect)

'+++++++++++++ SQL Server connections, command objects +++++++++++++++
Set oConn = New ADODB.Connection
Set oRS = New ADODB.Recordset
Set oComm = New ADODB.Command

oConn.Open strConn

oComm.ActiveConnection = oConn
oComm.CommandType = adCmdText

Do While Not objRS.EOF

strInsert = "INSERT INTO Contacts (firstname, lastname, email,address,
city, state, zip, phone) VALUES" & _
"('" & objRS("firstname") & "', '" & objRS("lastname") & "', '"
& objRS("email") & "', '" & objRS("address") & "'," & _
"'" & objRS("city") & "', '" & objRS("state") & "', '" &
objRS("zip") & "'," & _
"'" & objRS("phone") & "')"

oComm.CommandText = strInsert
oComm.Execute

objRS.MoveNext

Loop

'--------- when error what to do? ----------------
UpdateAwardErr:
If Err.Number <> 0 Then
MsgBox Err.Number & Err.Description
Exit Sub
Else
MsgBox "Records were successfully inserted", vbInformation,
"Contacts Update"
Exit Sub
End If
-------------------------------------------------end of
code----------------------------------

but I am unable to figure out how to get rid of the single quotes parsing
error if a column already contains an embedded quote?. I tried using the
escape character \\'\ but it didn't work! Kindly let me know what I am doing
wrong?

I greatly appreciate your time and help!

Thanks,
Jo


"Brendan Reynolds" wrote:

>
> 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)
> >>
> >>
> >>
>
>
>
.

Relevant Pages

  • Re: Import from excel, etc.
    ... do this...", so I first determined that I was, indeed, wrong about the string ... "Brendan Reynolds" wrote: ... Dim tdfs As DAO.TableDefs ... Dim flds As DAO.Fields ...
    (microsoft.public.access.externaldata)
  • Error Trap Connection to SQL server
    ... I am trying to establish links from Ms Access to tables in a SQL server ... the database is attached ... Dim rs1 As DAO.Recordset ... Dim strLinkName As String ...
    (microsoft.public.access.security)
  • Help with Error Trapping Connection to SQL server
    ... I am trying to establish links from Ms Access to tables in a SQL server ... the database is attached ... Dim rs1 As DAO.Recordset ... Dim strLinkName As String ...
    (microsoft.public.access.modulesdaovba)
  • VBA ConnectionTimeout problems
    ... Server is MS SQL Server 2000 ... Dim connDB As New ADODB.Connection 'connection to SQL Server ... Dim strTimeDay As String ...
    (microsoft.public.excel.programming)
  • Re: Help with MS Access Error Trap
    ... your access code and is not a SQL Server issue per se. ... > Dim rs1 As DAO.Recordset ... > Dim strLinkName As String ... > ' Now remove previous link info ...
    (microsoft.public.sqlserver.programming)