?
Solved

Accessing a linked table

Posted on 1998-02-01
10
Medium Priority
?
252 Views
Last Modified: 2010-08-05
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?
0
Comment
Question by:garik
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 2
10 Comments
 

Expert Comment

by:DonHillsberry
ID: 1967188
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
 
LVL 3

Author Comment

by:garik
ID: 1967189
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
 

Expert Comment

by:DonHillsberry
ID: 1967190
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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 12

Expert Comment

by:Trygve
ID: 1967191
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
 
LVL 3

Author Comment

by:garik
ID: 1967192
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
 
LVL 12

Expert Comment

by:Trygve
ID: 1967193
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
 
LVL 3

Author Comment

by:garik
ID: 1967194
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
 
LVL 12

Expert Comment

by:Trygve
ID: 1967195
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
 
LVL 3

Author Comment

by:garik
ID: 1967196
You're right about "!" vs "." - it solved the problem.
Please pick up the points. Thanks!

0
 
LVL 12

Accepted Solution

by:
Trygve earned 400 total points
ID: 1967197
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

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question