use ODBC in VB to read from a linked access db? 1

[hardcoder]

how do I use ODBC in VB to read from a linked access db?

I have a access db with a table that is actually a linked table to the real table on the network.

i made a VB app, with ADO 2.5, and a system DSN to read from this table. I couldn't and got an error that says "Expression required" or "Too few paramaters : required 1" or something.

I think i read somewhere that u can't use ODBC to connect to a DB that has a linked table.

What is the solution??

Pls help. This is urgent.
thanks Lyra Computing

http://www.lyracomputing.com?from=tektips

 

[cmmrfrds]

What is the reason that you don't directly access the table instead of going to the access db that has the linked table? Why can't you directly read the real table? What is your query intended to accomplish?

 

[jimmythegeek]

Too few parameters is usually a syntactical error. Can you post your SQL? Jim Lunde
compugeeks@hotmail.com
We all agree your theory is crazy, but is it crazy enough?

 

[demoman]

In concert with the two other posters, you definitely CAN do this. Mr Lunde is correct to suspect this is a syntax error.

I have a Pervasive SQL based system, which uses a third-party proprietary ODBC driver. I connect an Access DB to through a DSN. I have a VB program which connects to the Access data through ADO objects. However, I am about ready to abandon this configuration for a direct Pervasive SQL/VB approach.

The reason for including Access was because the ODBC was a DOG! It was slooooow

http://www (sometimes

timed out). It did not support scalar functions. It was the world's smallest toolbox, with the biggest performance overhead. Access would have handled this easily if the data was native.

I also have some apps which use a combination of data sources (Access, SQL Server, Oracle and Pervasive). I even use a combination of DAO and ADO.

Good Luck!

Most things in life are mind over matter. If you don't mind, it doesn't matter. (from Mark Twain)

 

[hardcoder]

thanks for the replies guys! i am sorry for replying late - and i still need the help!

cmmrfrds - i don't have control over the databsae to be able to change it - i HAVE to use the linked table in the db.

jimmythegeek - i think you are right to an extent. i changed the query to SELECT * instead of SELECT (fields) and don't get that error any longer.

However, I need more help. FYI, access to the table requires a login on the lan to be done. How do I accomplish this login via code/in the odbc dsn, since I sometimes get an error that says ' the database may be opened by another user or you need permission '.

Also, Like demoman said, can ODBC timeout here? When I query the access database by opening it in MS access itself, the queries take a long time to get the data, from 10-40 min. for 100-350 records (with about 20 fields).
How do I detect if this is due to a timeout??

Please help! A lot depends on this for me professionally! Tough times!

Thanks very much.


Lyra Computing

http://www.lyracomputing.com?from=tektips

 

[hardcoder]

whoops! a new error:

Error - 3265 - Item cannot be found in the collection corresponding to the requested name or ordinal

I just used a new query that goes SELECT TOP 1 * FROM ...

investigating... meanwhile pls let me have your input!
thanks Lyra Computing

http://www.lyracomputing.com?from=tektips

 

[cmmrfrds]

My understanding of what you have setup from your explanation is.
x.mdb
y.mdb
z.mdb
You are in z.mdb and want to read tables from y.mdb that are linked from x.mdb.

If this is the case then, setup an ADO connection to the x.mdb and read the tables directly without the linking.

Also, to help with your select statement please include the code along with how you set it up.

"the queries take a long time to get the data, from 10-40 min. for 100-350 records (with about 20 fields)."

How many records are in the table that the 350 records are pulled from?

 

[hardcoder]

thanks for replying!

yes, it is something like -
a local copy local.mdb that has a table tblA
tblA is a linked table to a table tblA in Remote database remote.mdb

I have a VB prog and ASP page, that use ODBC to connect using an MS Access System DSN on my machine that points to local.mdb.

There must be about 10000-12000 records in all.

Code for the connection (dsnTemp is the DSN):
dbconn.ConnectionString = "PROVIDER=MSDASQL;dsn=dsnTemp;"
dbconn.Open

When I used to do "SELECT field1, field2.. " I used to get the error - "Too few parameters. Expected 1." or something like that.
So I changed it to "SELECT *" but still can't query.
Probs:
1. Sometimes shows the error "Error - 3265 - Item cannot be found in the collection corresponding to the requested name or ordinal"
2. Sometimes takes too long even if I force a query that can return only one record - can i have a status label that continously shows the status whether data is actually being fetched? FYI, the call that blocks is tmpRst.open sql , and there's a lot of activity on the network icon when the call blocks.
3. Suggest a way to find out where the problem lies - the code/dsn/lan connection?
4. Can I test the ODBC connection with some utility? please suggest. Lyra Computing

http://www.lyracomputing.com?from=tektips

 

[cmmrfrds]

It looks like you are using the wrong ADO provider, the one you have is for SQL Server but even then there is a better one to use.. First, I recommend making a direct ADO connection to your remote mdb. Use the user data link wizard to do this and get the correct syntax. You will see a list of all the providers available on your PC - use the Microsoft Access one for your purposes.

Here is how you invoke the udl wizard. Do this on your desktop.

1. create a blank notepad file.
2. save the file.
3. rename the file by adding a new extention of .udl
make sure you save as all files not txt or some other
file type.
4. the icon for the file should change from notepad to a
little computer - it has now become the wizard.
5. double click on the file and it will bring up a dialog
box with multipule tabs.
6. use the microsoft access provider.
7. look under the ALL tab and there will be parameter settings which you can change if necessary. There is a test
button, press to test the connection.
8. close the file.
9. open the file from notepad instead of double clicking the icon. You will see the connection string which you can copy and paste into your program.

 

[hardcoder]

thanks for your suggestions!

i've tried to circumvent the prob by getting direct readonly access to the db.

here's my new prob:
thread181-258016 for your help once again! Lyra Computing

http://www.lyracomputing.com?from=tektips