Windows 2000 ODBC Connect

[Former Member]

I am a Sybase novice. I have created a DSN ("Uid=Username;Pwd=Password;DSN=DSNName" on Windows 2000. With Microsoft Access I can "Link" to the database and view the database, the tables, their fields and contents of each field. So far so good. I can connect to the database from Excel VBA using the DSN and the SQL "Select * from sysobjetcs where type = 'U'"; this SQL returns a number of user tables. As example "appt" is one of the user table returned. But when I try to SQL using "Select * from appt", I get an error that the table is not found. I have read a ton of documentation none of which has helped, there must be a simple fundamental piece of information I am missing. Thanks in adance for yout time and help.

[Former Member]

Figured it out, the connect from VBA needs to be fully qualified. "Select * from DSNName.Owner.TabelName"
example: "Select * db1.dba.tablename" what a hassle to figure this out.

[MarkCulp]

So just to be clear, this was a question about how to use Excel, not SQL Anywhere? Am I right?

[Former Member]

Actually it is a question and answer related to a DSN connection on Windows 2000 using Visual Basic for Applications (VBA). The connection via VBA to a Sybase DSN table requires a fully qualified string, "Select * DSNName.SybaseOwner.tablename". "Select * tablename" will not work. So this answer applies to Windows 2000 DSN, Excel VBA and the use of Sybase (Adaptive Server Anywhere 9.0). Hope this helps. Connecting to the Sybase (Adaptive Server Anywhere 9.0) DSN using Microsoft Access via “Linked Tables” will also work properly and you will have the ability to view tables, fields and contents.

[VolkerBarth]

Note, do you use the same UID to connect from Excel/VBA and Access?

If not, the behaviour you see might be expected since a user may or may not be able to access other user's tables without qualifying the owner name. (That depends on whether the owner is a group and whether the connecting user is a member of that group.)

If you do use the same UID, then I guess this is primarily an Excel/VBA issue.