Problem filling an ADO recordset from SQL Server

[msprygada]

I am having a problem with getting a recordset to fill with data in an
Access Data Project from a SQL Server database.

Here is the code example that is in the Access help files that I can
get to work just fine:

Dim Cnxn As ADODB.Connection
Dim rstEmployees As ADODB.Recordset
Dim strCnxn As String
Dim strSQLEmployees As String
Dim varDate As Variant

' Open connection
strCnxn = "Provider='sqloledb';Data Source='MySqlServer';" & _
"Initial Catalog='Pubs';Integrated Security='SSPI';"
Set Cnxn = New ADODB.Connection
Cnxn.Open strCnxn

' Open employee table
Set rstEmployees = New ADODB.Recordset
strSQLEmployees = "employee"
rstEmployees.Open strSQLEmployees, Cnxn, adOpenKeyset,
adLockOptimistic, adCmdTable

The only thing I changed was the Data Source from "MySqlServer" to the
name of our sql server and and this works and pulls in 43 records as
contained in the pubs database example.

So I changed the Inital catalog from "Pubs" to my database on the same
server and changed the table name from "employee" to my table name on
that server and when I run that, it returns back no records when there
is thousands of records in this table. Even thought it returns back no
records, looking at the object in debug mode shows that is knows the
field names of the table so I know that is it connecting correctly to
the database and the table.

My security in sql server is administrator and thus have access to all
database on this server.

Any ideas why I can connect to the text example and return data and not
my production data?

Thanks in advance.....

[msprygada]

Anyone?

[Rich P]

for your code here

' Open connection
strCnxn = "Provider='sqloledb';Data Source='MySqlServer';" & _
"Initial Catalog='Pubs';Integrated Security='SSPI';"
Set Cnxn = New ADODB.Connection
Cnxn.Open strCnxn

' Open employee table
Set rstEmployees = New ADODB.Recordset
strSQLEmployees = "employee"
rstEmployees.Open strSQLEmployees, Cnxn, adOpenKeyset,
adLockOptimistic, adCmdTable

try it this way:
--------------------------------------------------
' Open connection
strCnxn = "Provider=SQLOLEDB;Data Source=MySqlServer;" & _
"Initial Catalog=Pubs;UID=SA;PWD=;"
Set Cnxn = New ADODB.Connection
Cnxn.Open strCnxn

' Open employee table
Set rstEmployees = New ADODB.Recordset
rstEmployees.CursorLocation = adUseClietn '<--need this
rstEmployees.Open "Employees", Cnxn, adOpenDynamic,
adLockPessimistic, adCmdTable
For i = 1 to 10
Debug.print rstEmployees(0)
Next

------------------------------------

First, I changed your connection string, don't use single quote in the
connection string, plus, you need to specify the UID and PWD as I did
(whatever your UID and PWD are - UID can default to SA, and PWD can be
blank or not PWD=;). Then I specified the cursor location property of
the recordset object. Then I hardcoded your tablename, changed
adOpenKeyset to adOpenDynamic, adLockPessimistic. I won't promise you
anything because I have also had problems with the ADo recordset object.
I usually read data into a recordset from a stored procedure. Much
faster. Let me know if this works for you.
Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[msprygada]

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[msprygada]

Rich, Thanks for the reply.

I have this working now. I am currently filling my recordset from a
stored procedure and I have this recordset as the data source for a
form.

Now that I have this working, what I ultimately need to do is before I
fill the from with this data, I need to update one field in all records
of this recordset. This data contains a "from" and a "to" zip code. We
have a mileage application that has API's that we can pass zip codes to
and it will return the driving distance between those two zip codes.
What I need to do is fill the recordset with data from my SP and then go
thru each record in the recordset and extract the "to" and "from" zip
codes, pass it to the API and take the returning distance value and
update the distance field in the recordset. But every attempt to do this
ends up with the error "current recordset does not support updating". I
have tried every possible combination of the objRecordset.Open command.
Here is the latest one "rstEmployees.Open strSQLEmployees, Cnxn,
adOpenDynamic, adLockOptimistic, adCmdStoredProcs"
Any ideas as to how to update a recordset? Is it even possible with a
recordset that is created from a stored procedure? Should I be doing
this a different way? Since this is recordset is not "Connected" to a
table per say, is it possible to update a recordset like this? My
thought that this recordset was like a VB.NET dataset and I could add,
update, and change anything I wanted, but it appears not.
Thanks in advance for any help.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Rich P]

Hello again,

Without seeing what you are doing I can only explain in the blind. So I
will start by synchronizing with your stored procedure. Here is a
typical stored procedure in Sql Server (2000)
-------------------------------------------------
CREATE PROCEDURE [stp_PullZipcodeMilageData]

As

Select * From tblZipcodeMilage --your table in Sql Server

Go
-------------------------------------------------

And here is how you call the SP from an Access Module. First, Make sure
that you have the exact same table in Access, empty of course. The
easiest way to get the exact same table into Access is to export it from
Sql Server using DTS. Actually, this is the easiest way to pass the
data to Access. But I assume you want to automate this since the table
continues to grow. So now you have a table in Sql Server and the Exact
same table in Access. Note: In the Access table you can add one
additional field to the very end of the table where you will eventually
store your Milage data from the 2 zipcodes.

Here is a Sub to populate your Access Table with the Sql Server SP.
Make sure you have a reference to Mdac2.6 in Tools/References (in any
code module in Access). Make sure you have Mdac2.5 and 2.6 loaded on
your calling computer (need Mdac2.5 because it has the Jet interface for
Access - Mdac2.6 does not but has an upgraded ADO interface).

Sup GetZipcodeMilageData()
Dim cmd As New ADODB.Command, RSado As New ADODB.Recordset
Dim RSdao As DAO.Recordset
Dim i As Integer, j As Integer, RetVal As Variant

DoCmd.SetWarnings False
'empty out Access table first
DoCmd.RunSql "Delete * From tblZipCodeMilage"

cmd.ActiveConnection = Provider=SQLOLEDB;Data Source=yourServer;Initial
Catalog=yourSqlDB;UID=SA;PWD=;"

cmd.CommandTimeout = 60 'One minute - plenty of time for sp

cmd.CommandText = "stp_PullZipcodeMilageData"

Set RSado = cmd.Execute
DoEvents
Do While Not RSado.EOF
RSdao.AddNew
For i = 0 To RSado.Fields.Count - 1
RSdao(i) = RSado(i)
Next
RSdao.Update
RSado.MoveNext
j = j + 1
RetVal = SysCmd(acSysCmdSetStatus, j) 'monitor progress
Loop
RSado.Close
RSdao.Close
cmd.ActiveConnection.Close
End Sub

Now you have your Sql Server Data in Access. So now you can easily
manipulate this data in Access. You could actually do this in Sql
Server with a UDF (user Defined Function) and just pull the results from
the UDF (if you want to go there).

I assume your zipcode API is some custom function written in C. So you
make your API declaration and apply it like this where you supply the
arguments for your API function:

Sub UpdateZipCodeMilage()
DoCmd.RunSql "Update tblZipcodeMilage Set Milage =
yourAPIfunction(Zipcode1, Zipcode2)"
End Sub

Depending on how many records you have, say, 50,000, this should take a
few seconds. If you have say 5000 records or less, should take a few
miliseconds.
Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!