Recordset problem

[buzz]

Is there a limitation with the rst.OpenRecordset command when using linked
ODBC databases?? I'm trying to run commands to get data from the last record
of a table from an IBM DB2 database (opening this table requires the user to
enter login information). If I do the exact same commands to a local Access
table, it works just fine. Is there a know problem with this and what about
a workaround??

 

[Douglas J. Steele]

What method are you trying to use?

 

[buzz]

Dim dbs As Database
Dim Table As String
Dim EndDate As Long
Dim rst As Recordset
Dim CalendarDate As Long
Dim MinSerial As String
Dim MaxSerial As String
Dim Override As String
Dim strmessage As String

Function InsertRangeRecords()
Set dbs = CurrentDb
'User Input
'On Error GoTo TableInputError
Table = InputBox("Enter the Serial Range Table Name You Want To Add
Records To:", "Table")
Set rst = dbs.OpenRecordset(Table, dbOpenTable)

 

[Dirk Goldgar]

Dim dbs As Database
Dim Table As String
Dim EndDate As Long
Dim rst As Recordset
Dim CalendarDate As Long
Dim MinSerial As String
Dim MaxSerial As String
Dim Override As String
Dim strmessage As String

Function InsertRangeRecords()
Set dbs = CurrentDb
'User Input
'On Error GoTo TableInputError
Table = InputBox("Enter the Serial Range Table Name You Want To
Add Records To:", "Table")
Set rst = dbs.OpenRecordset(Table, dbOpenTable)
On Error GoTo 0

You can't open a table-type recordset (which is what dbOpenTable is
asking for) on a linked table. Use dbOpenDynaset (or dbOpenSnapshot, if
this is for read-only access) instead.

 

[buzz]

Ok, I'm trying to figure out what code I need to change to make this work and
am having problems working with the help menus to determine what needs to
adjust in my code to make this work with an ODBC database table. Can you
help (below is my program)??

Option Compare Database
Dim dbs As Database
Dim Table As String
Dim EndDate As Date
Dim rst As Recordset
Dim CalendarDate As Date
Dim MinSerial As String
Dim MaxSerial As String
Dim Override As String
Dim strmessage As String

Function InsertRangeRecords()

Set dbs = CurrentDb

'User Input
On Error GoTo TableInputError
Table = InputBox("Enter the Serial Range Table Name You Want To Add
Records To:", "Table")
Set rst = dbs.OpenRecordset(Table, dbOpenDynaset)
On Error GoTo 0

EndDate = InputBox("Enter the Ending Calendar Date You Want To Finish
With (mm/dd/yy):", "Calendar Date")

'Get values from last record in the table
With rst
.MoveLast
CalendarDate = CLM_RECEIVED_DT!
MinSerial = MIN_SERIAL_NBR!
MaxSerial = MAX_SERIAL_NBR!
Override = OVERRIDE_FLAG!
End With

'Loop to do the insertion of records to the ending calendar date inputted
Do Until CalendarDate = EndDate
CalendarDate = CalendarDate + 1
With rst
.AddNew
![CLM_RECEIVED_DT] = CalendarDate
![MIN_SERIAL_NBR] = MinSerial
![MAX_SERIAL_NBR] = MaxSerial
![OVERRIDE_FLAG] = Override
End With
Loop
rst.Close

TableInputError:
strmessage = MsgBox("You have entered an invalid table name. Please restart
the program.", vbOKOnly)

End Function

 

[buzz]

I forgot to note that the parts that are not working is the
setting/retrieving of values within the with statements and the loop
statement (so that's pretty much my whole code).

Ok, I'm trying to figure out what code I need to change to make this work and
am having problems working with the help menus to determine what needs to
adjust in my code to make this work with an ODBC database table. Can you
help (below is my program)??

Option Compare Database
Dim dbs As Database
Dim Table As String
Dim EndDate As Date
Dim rst As Recordset
Dim CalendarDate As Date
Dim MinSerial As String
Dim MaxSerial As String
Dim Override As String
Dim strmessage As String

Function InsertRangeRecords()

Set dbs = CurrentDb

'User Input
On Error GoTo TableInputError
Table = InputBox("Enter the Serial Range Table Name You Want To Add
Records To:", "Table")
Set rst = dbs.OpenRecordset(Table, dbOpenDynaset)
On Error GoTo 0

EndDate = InputBox("Enter the Ending Calendar Date You Want To Finish
With (mm/dd/yy):", "Calendar Date")

'Get values from last record in the table
With rst
.MoveLast
CalendarDate = CLM_RECEIVED_DT!
MinSerial = MIN_SERIAL_NBR!
MaxSerial = MAX_SERIAL_NBR!
Override = OVERRIDE_FLAG!
End With

'Loop to do the insertion of records to the ending calendar date inputted
Do Until CalendarDate = EndDate
CalendarDate = CalendarDate + 1
With rst
.AddNew
![CLM_RECEIVED_DT] = CalendarDate
![MIN_SERIAL_NBR] = MinSerial
![MAX_SERIAL_NBR] = MaxSerial
![OVERRIDE_FLAG] = Override
End With
Loop
rst.Close

TableInputError:
strmessage = MsgBox("You have entered an invalid table name. Please restart
the program.", vbOKOnly)

End Function

Is there a limitation with the rst.OpenRecordset command when using linked
ODBC databases?? I'm trying to run commands to get data from the last record
of a table from an IBM DB2 database (opening this table requires the user to
enter login information). If I do the exact same commands to a local Access
table, it works just fine. Is there a know problem with this and what about
a workaround??

 

[Douglas J. Steele]

Your exclamation points are wrong. They need to be in front of the field
name (without the With rst, you'd be using rst!CLM_RECEIVED_DT)

With rst
.MoveLast
CalendarDate = !CLM_RECEIVED_DT
MinSerial = !MIN_SERIAL_NBR
MaxSerial = !MAX_SERIAL_NBR
Override = !OVERRIDE_FLAG
End With

Then, inside your loop, you're saving the record you're adding

Do Until CalendarDate = EndDate
CalendarDate = CalendarDate + 1
With rst
.AddNew
![CLM_RECEIVED_DT] = CalendarDate
![MIN_SERIAL_NBR] = MinSerial
![MAX_SERIAL_NBR] = MaxSerial
![OVERRIDE_FLAG] = Override

.Update ' <== This is missing

End With
Loop


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I forgot to note that the parts that are not working is the
setting/retrieving of values within the with statements and the loop
statement (so that's pretty much my whole code).

Ok, I'm trying to figure out what code I need to change to make this work and
am having problems working with the help menus to determine what needs to
adjust in my code to make this work with an ODBC database table. Can you
help (below is my program)??

Option Compare Database
Dim dbs As Database
Dim Table As String
Dim EndDate As Date
Dim rst As Recordset
Dim CalendarDate As Date
Dim MinSerial As String
Dim MaxSerial As String
Dim Override As String
Dim strmessage As String

Function InsertRangeRecords()

Set dbs = CurrentDb

'User Input
On Error GoTo TableInputError
Table = InputBox("Enter the Serial Range Table Name You Want To Add
Records To:", "Table")
Set rst = dbs.OpenRecordset(Table, dbOpenDynaset)
On Error GoTo 0

EndDate = InputBox("Enter the Ending Calendar Date You Want To Finish
With (mm/dd/yy):", "Calendar Date")

'Get values from last record in the table
With rst
.MoveLast
CalendarDate = CLM_RECEIVED_DT!
MinSerial = MIN_SERIAL_NBR!
MaxSerial = MAX_SERIAL_NBR!
Override = OVERRIDE_FLAG!
End With

'Loop to do the insertion of records to the ending calendar date inputted
Do Until CalendarDate = EndDate
CalendarDate = CalendarDate + 1
With rst
.AddNew
![CLM_RECEIVED_DT] = CalendarDate
![MIN_SERIAL_NBR] = MinSerial
![MAX_SERIAL_NBR] = MaxSerial
![OVERRIDE_FLAG] = Override
End With
Loop
rst.Close

TableInputError:
strmessage = MsgBox("You have entered an invalid table name. Please restart
the program.", vbOKOnly)

End Function

Is there a limitation with the rst.OpenRecordset command when using linked
ODBC databases?? I'm trying to run commands to get data from the last record
of a table from an IBM DB2 database (opening this table requires the user to
enter login information). If I do the exact same commands to a local Access
table, it works just fine. Is there a know problem with this and what about
a workaround??

 

[buzz]

Thanks for the response. I didn't realize I had those typos in the first
with statement and for the loop, I didn't know I had to use the update. For
some reason, when I used this similar code on a local access table, it seemed
to still work without the .update (maybe that's a difference between the ADO
and DAO tables or whatever that terminology is for external or linked
tables). Thanks again.

Your exclamation points are wrong. They need to be in front of the field
name (without the With rst, you'd be using rst!CLM_RECEIVED_DT)

With rst
.MoveLast
CalendarDate = !CLM_RECEIVED_DT
MinSerial = !MIN_SERIAL_NBR
MaxSerial = !MAX_SERIAL_NBR
Override = !OVERRIDE_FLAG
End With

Then, inside your loop, you're saving the record you're adding

Do Until CalendarDate = EndDate
CalendarDate = CalendarDate + 1
With rst
.AddNew
![CLM_RECEIVED_DT] = CalendarDate
![MIN_SERIAL_NBR] = MinSerial
![MAX_SERIAL_NBR] = MaxSerial
![OVERRIDE_FLAG] = Override

.Update ' <== This is missing

End With
Loop


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I forgot to note that the parts that are not working is the
setting/retrieving of values within the with statements and the loop
statement (so that's pretty much my whole code).

Ok, I'm trying to figure out what code I need to change to make this work and
am having problems working with the help menus to determine what needs to
adjust in my code to make this work with an ODBC database table. Can you
help (below is my program)??

Option Compare Database
Dim dbs As Database
Dim Table As String
Dim EndDate As Date
Dim rst As Recordset
Dim CalendarDate As Date
Dim MinSerial As String
Dim MaxSerial As String
Dim Override As String
Dim strmessage As String

Function InsertRangeRecords()

Set dbs = CurrentDb

'User Input
On Error GoTo TableInputError
Table = InputBox("Enter the Serial Range Table Name You Want To Add
Records To:", "Table")
Set rst = dbs.OpenRecordset(Table, dbOpenDynaset)
On Error GoTo 0

EndDate = InputBox("Enter the Ending Calendar Date You Want To Finish
With (mm/dd/yy):", "Calendar Date")

'Get values from last record in the table
With rst
.MoveLast
CalendarDate = CLM_RECEIVED_DT!
MinSerial = MIN_SERIAL_NBR!
MaxSerial = MAX_SERIAL_NBR!
Override = OVERRIDE_FLAG!
End With

'Loop to do the insertion of records to the ending calendar date inputted
Do Until CalendarDate = EndDate
CalendarDate = CalendarDate + 1
With rst
.AddNew
![CLM_RECEIVED_DT] = CalendarDate
![MIN_SERIAL_NBR] = MinSerial
![MAX_SERIAL_NBR] = MaxSerial
![OVERRIDE_FLAG] = Override
End With
Loop
rst.Close

TableInputError:
strmessage = MsgBox("You have entered an invalid table name. Please restart
the program.", vbOKOnly)

End Function

:

Is there a limitation with the rst.OpenRecordset command when using linked
ODBC databases?? I'm trying to run commands to get data from the last record
of a table from an IBM DB2 database (opening this table requires the user to
enter login information). If I do the exact same commands to a local Access
table, it works just fine. Is there a know problem with this and what about
a workaround??