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

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: multiplatform (pocketPC & desktopPC) (Daniel !!)
    ... Friend Versione As String ... Public Sub GetMyConnectionPalmare() ... Dim errorMessages As String ... Private Function GetDS_Desktop(ByVal SQL As String) As DataSet ...
    (microsoft.public.dotnet.framework.compactframework)
  • Re: multiplatform (pocketPC & desktopPC) (Daniel !!)
    ... Friend Versione As String ... Public Sub GetMyConnectionPalmare() ... Dim errorMessages As String ... Private Function GetDS_Desktop(ByVal SQL As String) As DataSet ...
    (microsoft.public.dotnet.framework.compactframework)
  • Re: Performance von SQL-Abfragen auf Exceltabellen
    ... Nun habe ich es mit SQL auf Exceldaten versucht und konnte das ganze ... Performance konstant bei rund 3 Sekunden. ... Dim iColumnCount As Long ... Dim FirstColumnforSQLexists_jn As String 'j if first column for SQL ...
    (microsoft.public.de.excel)
  • Re: Multi Field SQL Where Clause
    ... Trying to DIM variables in a public function when the variables are already DIM'd will generate an error. ... I also struggled with creating the SQL string when I first started. ... Yes, it should have been a single quote, not three quotes. ... sysNtDvNo = sysPUOHDvNo ...
    (microsoft.public.access.forms)
  • Re: Command Line
    ... Doubling of double quotes are ... Dim Args() As String ... Dim sCurrentArg As String ...
    (microsoft.public.vb.general.discussion)