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

Thanks a lot, it worked! - Jo

"Brendan Reynolds" wrote:

> In SQL you 'escape' single quotes by doubling them. In the example below,
> I've inserted spaces between the quotes just so that you can see which are
> single and which are double quotes, you should not include the spaces
> between the quotes in the real SQL statement ...
>
> ... & Replace(objRS("firstname"), " ' ", " ' ' ") & ...
>
> --
> Brendan Reynolds (MVP)
>
>
> "Jo" <Jo@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> news:F407B832-1DFE-4754-B822-92F33E53AA1F@xxxxxxxxxxxxxxxxx
> > 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: using VBA to insert records from access database to sql server
    ... In SQL you 'escape' single quotes by doubling them. ... > Dim oConn As ADODB.Connection ... > Dim strInsert As String ...
    (microsoft.public.access.modulesdaovba)
  • RE: Help with the following code
    ... In your sql i noticed you enclosed the integer with single quotes. ... One thing is when you try to pass a string (maybe you already know about ... >> Dim sql as String ...
    (microsoft.public.excel.programming)
  • Re: Which identity?
    ... > Some postings I read suggested using MAXto retrieve the inserted record ... > each of these 3 SQL features? ... Another way to do it if using the .AddNew method on a Jet based ADO ... Dim rsTest As ADODB.Recordset ...
    (microsoft.public.access.queries)
  • Re: display data to user question
    ... I created a project that has an embedded SQl table with the scenario you ... Dim cn As New SqlConnection("Data ... Protected Sub Page_Load(ByVal sender As Object, ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: display data to user question
    ... I created a project that has an embedded SQl table with the scenario you ... Dim cn As New SqlConnection("Data ... Protected Sub Page_Load(ByVal sender As Object, ...
    (microsoft.public.dotnet.framework.aspnet)
Quantcast