Creating a recordset from an ODBC linked table

[Ymesei]

Can anyone tell me what I am doing wrong? I have linked a table from an ODBC database to my Access database. I want to assign a value from a field in the first record of the table to a variable using a module. When I run the code it returns the message "Record is deleted." Here's the code that I'm using. Is is correct? It works for tables linked from another access database.

======================================
Dim cnt As Integer
Dim dbs as Database
Dim rstInvoiceNo As Recordset

Set dbs = CurrentDb
Set rstInvoiceNo = dbs.OpenRecordset("AR0_Parameters&quot

rstInvoiceNo.MoveFirst
cnt = rstInvoiceNo.Fields("NextAutomaticInvoiceNumber&quot
======================================

The table AR0_Parameters is linked to my access database from an ODBC compatible database.

 

[DougP]

What Error message are you getting.

DougP, MCP
dposton@universal1.com

Ask me how Bar-codes can help you be more productive.

 

[Ymesei]

When I ran the code, a window popped up that said "Record is deleted." and an okay button. The code would stop there. It would happen on the line used to assign the value to variable cnt.

Actually, I don't think there was anything wrong with my code. For some reason, I wasn't able to pick only the first record's data. Even if I ran a query trying to filter out all other records besides the first record, I would get a record with [#Deleted] in the cell. I was able to make it work by initializing the recordset this way:
=========================================
Set rstInvoiceNo = dbs.OpenRecordset("SELECT First(AR0_Parameters.NextAutomaticInvoiceNumber) AS Expr1 FROM AR0_Parameters;&quot

rstInvoiceNo.MoveFirst
cnt = rstInvoiceNo.Fields("Expr1&quot
=========================================

So I'm assuming that the problem lied in the values that were being returned by the table.

I don't know why it's like that though. Any insight would be great.

Thanks.

 

[harryrich]

Many of my problems with externally linked tables have been due to making design changes in the external tables without refreshing the links.

Whenever I have a problem, whether through ODBC or with an external Access table, I run the linked table manager and refresh the links before trying other actions to debug. It saves me a lot of trouble.

Best,

Harry