Accessing a linked table

I've linked a table in the external MS Access database using
File->Get External Data->Link Tables
I can browse this table and run queries on it, but I can't access it from VB code - it gives me "Member not found" error. The code runs fine on the imported table.
Do I have to do something special to treat a linked table in my code?
LVL 3
garikAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DonHillsberryCommented:
Please provide more information..
What version of Access and VB are you using?
What version of Access was used to create the database?
What type of database was used to create the table getting linked?

How are you accessing the database from VB?  Are you using a data control or DAO?

What exactly are you trying to do with the table?  Are you simply doing a select to retrieve records?
0
garikAuthor Commented:
MS Access 97 is used for everything.
I might use a wrong term, but by VB I meant Visual Basic used to create procedures in the Access module.
I need to get a number of records in the linked table first and then go through each record and do some processing.
0
DonHillsberryCommented:
I have used linked tables from a VB program and never experienced a problem.  There are retrictions on modifying the table definition, but I haven't had any problems accessing data.

I have not tried accessing linked tables from VBA within Access, but I would be surprised if you have to do anything special within your code.

I don't think I'm going to be much help for you on this one.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

TrygveCommented:
Could you provide the code, or parts of it, that gives you the problem. I have worked a lot on linked tables and could perhaps be of some assistants.
0
garikAuthor Commented:
Function splitStyleOptions()
Dim db  As Database
Dim rsOptions As Recordset, rsFeatures As Recordset, rsIDs As Recordset, rsStyles As Recordset
Dim strBuf As String, tok As String
Dim i As Long, j As Long, m As Long, n As Long, cnt As Long

Set db = CurrentDb()
'
' Empty Styles-Features table
db.Execute ("delete from [Available IDs]")
'
Set rsIDs = db.OpenRecordset("Available IDs")
Set rsFeatures = db.OpenRecordset("Available Features", dbOpenTable)
Set rsOptions = db.OpenRecordset("select count(*) as RecNum from [Options]")
m = SysCmd(acSysCmdInitMeter, "Processing Available Features..", rsOptions.RecNum)
rsOptions.Close

...

Execution stops with a message: "Method or data member not found" and rsOptions.RecNum highlighted (in SysCmd statement). It always stops at the first call to a member of rsOptions.
0
TrygveCommented:
It seems to me like it is the RecNum that gives you the problem and not the linked table itself.

If you are trying to set the status bars maximum number you could try the following.

rsOptions.movelast ' Ensure correct record number
m = SysCmd(acSysCmdInitMeter, "Processing Available Features..", rsOptions.RecordCount) ' Set to the number of the last record.

rsOptions.movefirst ' Go to the top again.

...


Hope this helps !
Trygve
0
garikAuthor Commented:
Nope. As I said in a previous comment, execution stops after the first call to a member of rsOptions - if it's not RecNum as in my sample code, then it's a first member accessed in a subsequent selects. Sorry.

0
TrygveCommented:
Sorry about. In my eager to answer the question I read your comment about getting the number of records and from there came my answer.

Remember when refering to fields in a table/recordset object you should use MyRecs!Field instead of MyRecs.Field   The "." is reserved for properties and events and the "!" is for field references.


What happens if you make a query with the same SQL statement "select count(*) as RecNum from [Options]" Does it return anything ?
0
garikAuthor Commented:
You're right about "!" vs "." - it solved the problem.
Please pick up the points. Thanks!

0
TrygveCommented:
Thanks a lot. OK here it is:

Remember when refering to fields in a table/recordset object you should use MyRecs!Field instead of MyRecs.Field The "." is reserved for properties and events and the "!" is for field references.

Have a nice day !
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.