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.

asked 19 Jun '11, 19:36

RayInAZ's gravatar image

RayInAZ
0111
accept rate: 0%

edited 20 Jun '11, 12:15

Mark%20Culp's gravatar image

Mark Culp
24.8k9139296


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.

permanent link

answered 19 Jun '11, 20:23

RayInAZ's gravatar image

RayInAZ
0111
accept rate: 0%

1

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

(20 Jun '11, 09:28) Mark Culp

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.

(20 Jun '11, 11:07) RayInAZ
Replies hidden

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.

(20 Jun '11, 12:18) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×157
×17
×14
×2

question asked: 19 Jun '11, 19:36

question was seen: 2,851 times

last updated: 20 Jun '11, 12:19