SQL connection and recordset

[abenitez77]

I am not able to get values into a recordset. The first sql command to insert the records work, but when i try to retrieve the Max(ID), the recordset is Empty.



Dim rst2 As DAO.Recordset

Set Connection2 = CreateObject("ADODB.Connection")

sServer = "SAS01SRVSQ01\SASDATA"
sDBName = "UNFI_Temp"
ConnectionString = _
"Provider=SQLOLEDB;" & _
"Data Source=" + sServer + ";" & _
"Initial Catalog=" + sDBName + ";" & _
"Integrated Security=SSPI"

Connection2.Open ConnectionString


'Insert Header info into XlsData
tSQL = "Insert into XLSData (SupplierName, SupplierNumber, BuyDateS, ShipDateS, FilePath, FileName) "
tSQL = tSQL & "VALUES ('" & Supplier & "','" & SupplierNo & "','" & BuyDate & "','" & ShipDate & "','" & xFilePath & "','" & xFileName & "') "

Set rst2 = Connection2.Execute(tSQL)

rst2.MoveLast
rst2.MoveFirst

tSQL = "Select Max(ID) as IDMax From XLSData"
Set rst2 = Connection2.Execute(tSQL)
rst2.MoveLast
rst2.MoveFirst

myID = rst2.IDMax

 

[Norie]

Try opening the recordset.

Set rst2 = CreateObject("ADODB.RecordSet")

rst.Open tSQL, Connection2.

 

[abenitez77]

Try opening the recordset.

Set rst2 = CreateObject("ADODB.RecordSet")

rst.Open tSQL, Connection2.

I tried this and gave me an error: Method or Data Member not found:

and highlighted ".Open"

 

[abenitez77]

ok, i fixed that part...and in the watch I can see the recordset but how do I get the value from the recordset into a variable? I tried the syntax below..but it did not get the value.

myID = rst.IDMax

 

[vds1]

Try this,

tSQL = "Select Max(ID) as IDMax From XLSData"
if rst2.state=1 then rst2.close
rst2.Open tSQL, Connection2
rst2.MoveLast
rst2.MoveFirst

myID = rst2.fields(0)

 

[vds1]

ok, i fixed that part...and in the watch I can see the recordset but how do I get the value from the recordset into a variable? I tried the syntax below..but it did not get the value.

myID = rst.IDMax

myID=rst2.fields(0)

 

[abenitez77]

I still can't get the value of IDMax into a variable.

 

[abenitez77]

myID=rst2.fields(0)

I didn't see this last reply from you. This worked Thanks!