create temp local table from ado recordset

[heatherb0123]

I'm trying to create local Access tables for data linked via ODBC from Sybase (I know the SQL code connecting to Sybase is correct. I tested it in a diff module and it returns the exact # of records). I would like to create tables within access, but I can't get the code to work. Any help would be appreciated. Below is my code. When I step through the code, I notice that when it gets to "Do While Not rsSQL.EOF" it skips to the end of the code as if the recordset is already at end the of file???
````````````````````````````````````````````````````````````````````````````````````````````````````````````````````````
Function GetHiNet_Data()

'Define and open connection to SQL
Dim conSQL As ADODB.Connection
Set conSQL = New ADODB.Connection
conSQL.Open "DRIVER={SYBASE ASE ODBC Driver};NA=GPSURS,
3000;UID=bssapp;PWD=bssapp;"

'Define SQL command
Dim cmdSQL As ADODB.Command

Set cmdSQL = New ADODB.Command

cmdSQL.ActiveConnection = conSQL

cmdSQL.CommandText = "SELECT trp_holdrefN.acid, pr_account.acnomajor,
pr_account.acnominor, trp_holdrefN.smsecid, pr_secxref.sxvalue, trp_holdrefN.
quantity, trp_holdrefN.cost_base, trp_holdrefN.cost_local, trp_amtbaseN.
lcl_amort_cost " _
& "FROM pr_account INNER JOIN trp_holdrefN ON pr_account.acid = trp_holdrefN.
acid INNER JOIN pr_secxref ON trp_holdrefN.smsecid = pr_secxref.smsecid INNER
JOIN trp_amtbaseN ON trp_holdrefN.effdate = trp_amtbaseN.effdate AND
trp_holdrefN.smsecid = trp_amtbaseN.smsecid AND trp_holdrefN.acid =
trp_amtbaseN.acid WHERE pr_secxref.sxitm = 2 And pr_secxref.sxenddate > '" &
rptdte & "' And trp_holdrefN.effdate ='" & rptdte & "' " _
& "GROUP BY trp_holdrefN.acid, pr_account.acnomajor, pr_account.acnominor,
trp_holdrefN.smsecid, pr_secxref.sxvalue, trp_holdrefN.quantity, trp_holdrefN.
cost_base, trp_holdrefN.cost_local, trp_amtbaseN.lcl_amort_cost " _
& "HAVING trp_holdrefN.acid = 7929 Or trp_holdrefN.acid = 7939 Or
trp_holdrefN.acid = 7947 Or trp_holdrefN.acid = 7928 Or trp_holdrefN.acid =
7905 Or trp_holdrefN.acid = 7932 Or trp_holdrefN.acid = 7936 Or trp_holdrefN.
acid = 7930 Or trp_holdrefN.acid = 8313 Or trp_holdrefN.acid = 8315 Or
trp_holdrefN.acid = 9558 " _
& "ORDER BY trp_holdrefN.acid, trp_holdrefN.smsecid"

cmdSQL.CommandType = adCmdText

'Define and open recordset returned from SQL
Dim rsSQL As ADODB.Recordset
Set rsSQL = New ADODB.Recordset
rsSQL.Open cmdSQL

'Define connection to the local Access Database
Dim conLocal As ADODB.Connection
Set conLocal = CurrentProject.Connection

'Define Local command 1 and execute it - clear down tblHiNet_Data
Dim cmdL1 As ADODB.Command
Set cmdL1 = New ADODB.Command
cmdL1.ActiveConnection = conLocal
cmdL1.CommandText = "Delete from tblHiNet_Data"
cmdL1.CommandType = adCmdText
cmdL1.Execute

'Define Local command 2 and execute it - create empty local recordset
Dim cmdL2 As ADODB.Command
Set cmdL2 = New ADODB.Command
cmdL2.ActiveConnection = conLocal
cmdL2.CommandText = "select * from tblHiNet_Data"
Dim rsL As ADODB.Recordset
Set rsL = New ADODB.Recordset
rsL.Open cmdL2, , adOpenDynamic, adLockOptimistic

' Loop around the SQL recordset to populate the local recordset and update
tblHiNet_Data
Dim intCount As Integer

Do While Not rsSQL.EOF 'Recordset is at end of file???
rsL.AddNew
For intCount = 0 To 2000
rsL.Fields(intCount) = rsSQL.Fields(intCount)
Next intCount
rsL.Update
rsSQL.MoveNext
Loop

'Clean up
Set conSQL = Nothing
Set conLocal = Nothing
Set cmdSQL = Nothing
Set cmdL1 = Nothing
Set cmdL2 = Nothing
Set rsSQL = Nothing
Set rsL = Nothing

End Function

 

[vbajock]

cmdL1.CommandText = "Delete from tblHiNet_Data"
cmdL1.CommandType = adCmdText
cmdL1.Execute

empties your record set.

When you issue

cmdL2.CommandText = "select * from tblHiNet_Data"
Dim rsL As ADODB.Recordset
Set rsL = New ADODB.Recordset
rsL.Open cmdL2, , adOpenDynamic, adLockOptimistic

you then open that empty record set.

When you issue

Do While Not rsSQL.EOF

it is going to immediately exit, because rsSQL is indeed at the end of the file, since there are no records in it, because you whacked them all.

 

[heatherb0123]

rsSQL" shouldn't be empty because the data is coming from "rsSQL.Open cmdSQL" which is the T-SQL code pulling the data from Sybase. I do want to delete all records from "rsL" (record set Local i.e. "tblHiNet_Data"), however "rsSQL" should have data in it. Can you tell me how to go about changing this code to get it to work? Or possibly refer me to some other code that will do the same thing as I'm trying to accomplish? Thanks soo much for your help!


Heather B...

 

[vbajock]

Oh, sorry, I missed that, if that is the case, then your SQL statement isn't returning any records and you need to test it. If you want to make double sure put this into your code:



if rsSQl.eof=true and rsSql.Bof = true then


Stop

Endif

right before the

Do While Not rsSQL.EOF

statement

When it hits the stop, then both Eof and Bof are true, which means your SQL returned an empty table.

Myself, I usually use code that looks like this:

Set rsL = New ADODB.Recordset
rsL.Open cmdL2, , adOpenDynamic, adLockOptimistic

if rsSQl.eof=true and rsSql.Bof = true then
-> put code in here to handle an empty recordset, or troubleshoot SQL statement if I know for sure I should have had records

else
-> I've got at least one record
Do
-> do stuff to my records now that I know I have records for sure
Loop until RsSql.eof

Endif

b

 

[JoeAtWork]

I know the SQL code connecting to Sybase is correct. I tested it in a diff module and it returns the exact # of records

Are you sure the SQL statement you are using is exactly the same as the one in your different module? Does the variable rptdte evaluate to the same thing in the two different modules?

Everyting is pointing to the query not actually returning any records.

 

[heatherb0123]

Below is the code where I verified the rsSQL is pulling the correct # of records from Sybase. When I run this it prints the exact amount of records that should be put into the local Access tables. When I use this same SQL Statement in my main code (above), it doesn't work.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Function TestHiNetConnection()
Dim rptdte As Date

Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset

rptdte = [Forms]![frmAmortVariances]![cboReportDate]

Set cnn = New ADODB.Connection
Set rs = New ADODB.Recordset

With cnn
.ConnectionString = "DRIVER={SYBASE ASE ODBC Driver};NA=GPSURS,3000;UID=bssapp;PWD=bssapp;"
.CursorLocation = adUseClient
.Open
End With

With rs
.ActiveConnection = cnn
.Source = "SELECT trp_holdrefN.acid, pr_account.acnomajor, pr_account.acnominor, trp_holdrefN.smsecid, pr_secxref.sxvalue, trp_holdrefN.quantity, trp_holdrefN.cost_base, trp_holdrefN.cost_local, trp_amtbaseN.lcl_amort_cost " _
& "FROM pr_account INNER JOIN trp_holdrefN ON pr_account.acid = trp_holdrefN.acid INNER JOIN pr_secxref ON trp_holdrefN.smsecid = pr_secxref.smsecid INNER JOIN trp_amtbaseN ON trp_holdrefN.effdate = trp_amtbaseN.effdate AND trp_holdrefN.smsecid = trp_amtbaseN.smsecid AND trp_holdrefN.acid = trp_amtbaseN.acid WHERE pr_secxref.sxitm = 2 And pr_secxref.sxenddate > '" & rptdte & "' And trp_holdrefN.effdate ='" & rptdte & "' " _
& "GROUP BY trp_holdrefN.acid, pr_account.acnomajor, pr_account.acnominor, trp_holdrefN.smsecid, pr_secxref.sxvalue, trp_holdrefN.quantity, trp_holdrefN.cost_base, trp_holdrefN.cost_local, trp_amtbaseN.lcl_amort_cost " _
& "HAVING trp_holdrefN.acid = 7929 Or trp_holdrefN.acid = 7939 Or trp_holdrefN.acid = 7947 Or trp_holdrefN.acid = 7928 Or trp_holdrefN.acid = 7905 Or trp_holdrefN.acid = 7932 Or trp_holdrefN.acid = 7936 Or trp_holdrefN.acid = 7930 Or trp_holdrefN.acid = 8313 Or trp_holdrefN.acid = 8315 Or trp_holdrefN.acid = 9558 " _
& "ORDER BY trp_holdrefN.acid, trp_holdrefN.smsecid"
.Open

End With

Debug.Print rs.RecordCount

End Function


Heather B...

 

[vbajock]

Did you test it like I suggested?

 

[vbajock]

Also, you are opening rsSQL.Open cmdSQL

without setting the cursor type and the lock method- while that should work with the default methods of adOpenForwardOnly,adLockReadOnly you might want to try a different cursor type.

 

[heatherb0123]

Thanks for everyone's help. I figured it out. The error was in my loop statement. Below is the corrected code.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''

' Loop around the SQL recordset to populate the local recordset and update tblHiNet_Data

Dim intCount As Integer

If rs.EOF = True And rs.BOF = True Then
Debug.Print rs.RecordCount
Else
'-> I've got at least one record
Do
rsL.AddNew
For intCount = 0 To rs.Fields.Count - 1
rsL.Fields(intCount) = rs.Fields(intCount)
Next intCount
rsL.Update
rs.MoveNext
Loop Until rs.EOF
End If

Heather B...