using VBA to insert records from access database to sql server

[Jo]

Hi Everyone,

I am looking for some help in the following:

I have an access 2003 database. I am trying to provide an automation
facility where the users at the touch of a button can insert records from a
table to a sql server datatable. I am trying to use VBA and I also have set
up workgroup file for this so only users with this privilege can insert
records into sql server database. The schema of sql server looks similar to
that of access database where majority of the columns are text fields.

I get this error message when I am using odbc dsn-less connection:

Data source not found and no drivers specified which I couldn't understand
how?.

I changed this to OLE DB and tried but get this strange error: Record(s)
cannot be read; no read permission on contact

This is the code I have written to accomplish the task:

Dim oConn As ADODB.Connection
Dim oRS As ADODB.Recordset
Dim oComm As ADODB.Command

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

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

strSelect = "SELECT firstname,lastname,email,address,city,state,zip,phone
FROM contact"

objConn.Open strMDB

Set objRS = objConn.Execute(strSelect)

Set oConn = New ADODB.Connection
Set oRS = New ADODB.Recordset
Set oComm = New ADODB.Command

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

'Driver={SQL Server}; Server=ODTESTServer; Database=Contact; Uid=dummy;
Pwd=abc"

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("state") & "', '" & objRS("zip") & "', '" & objRS("phone") &
"')"

oComm.CommandText = strInsert
oComm.Execute

objRS.MoveNext

Loop

Exit_UpdateAward:
oConn.Close
oRS.Close
Set oConn = Nothing
Set oRS = Nothing
Exit Sub

UpdateAwardErr:
MsgBox Err.Number & Err.Description
Resume Exit_UpdateAward

Any help is greatly appreciated! Thanks in advance
-Jo

 

[Brendan Reynolds]

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

 

[Jo]

Brendan,

Thanks for your reply. I tried separating out initially and the connection
somehow was taking the default admin userid for which I have just the 'users'
privileges and hence was unable to test out my code.

However, when I tried changing the connection strings to OLE DB providers
again, I am getting a "number of columns in the INSERT statement not matching
with the VALUES list error". I checked the insert statement and found that
the columns do match.

I tried inserting with hard-coded values and it works but not from a query!!

Please help me!

Thank you-Jo

 

[Jo]

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

 

[Tim Ferguson]

However, when I tried changing the connection strings to OLE DB
providers again, I am getting a "number of columns in the INSERT
statement not matching with the VALUES list error". I checked the
insert statement and found that the columns do match.

I tried inserting with hard-coded values and it works but not from a
query!!

PMFJI, but looking at your original post, I think you might have missed
something:

INSERT INTO contacts (
firstname, lastname, email, address,
city, state, zip, phone
)
VALUES (
'SELECT *
FROM contact (Provider etc etc)'
)


The syntax is badly mangled. The VALUES clause is followed by one string
in single quotes beginning 'SELECT * FROM...' and finishing after the end
of the connection string. There is a spare single quote which I guess is
a cut-and-paste error because otherwise it won't even parse.

I think you really want to do this without the VALUES marker at all.

A further potential problem is using the * selector in an insert
statement. The list of fields does not contain any obvious PK or row
identifier, and I doubt that you will have got this far without having
one in. The only safe way is to list all the relevant fields in the right
order sic:

INSERT INTO contacts (
firstname, lastname, email, address,
city, state, zip, phone
)
SELECT
firstname, lastname, email, address,
city, state, zip, phone
FROM contact (Provider etc etc)


Hope that helps


Tim F

 

[Brendan Reynolds]

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.

 

[Jo]

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]

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"), " ' ", " ' ' ") & ...

 

[Jo]

Thanks a lot, it worked! - Jo

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"), " ' ", " ' ' ") & ...