How come nobody helps anyone with an ODBC question?

[MPBCHM]

I have been thru the FAQ's and Keyword Search and noticed almost all of the ODBC questions asked had no responses and the ones that do are vague. Is there something wrong with asking an ODBC question? Is that a taboo subject in Tek-Tips? Why doesn't anyone like to answer these questions?

Well here is my question anyway....

I created a linked table using the "Create Table Wizard". If I close the database and later reopen it and then try to open one of the linked tables I receive the following error message:

ODBC connection to 'ezcap Angeles1' failed

(after clicking "OK" the next message is)

Microsoft Access can't open the table in Datasheet view

Question(s):
1. What is causing these errors to occur?

2. How can I establish links to SQLBase tables using ODBC drivers successfully?

I am using Win2000 and Access 2000.


Thanks for the help...

Mark

 

[fredk]

I am far from an expert but I can tell you that I had to go into control panel to set up odbc

Under Administrative Tools - Click on data source (odbc) icon. In my situation, I had to select the system dsn tab then click the add button - Then follow the wizard to select the correct data source and file etc.

Again, I am far from an expert when it comes to odbc but thats how I got the initial setup - That creates it - Then I was able to go in to access and link it.

Hope that helps

 

[AccessMoron]

are you opening the DB on the same machine as the one you set upth link?

 

[B827]

You set up the ODBC connection using the Control Panel. Then in Access you use the following procedure
File
>Get External Data
>Link tables

Then in the dialog box in the bottom left corner select ODBC Databases from the "Files of Type" list box. Select your ODBC connection (as per set up in control panel) then link the tables you need. Getting data from linked tables can be slow, Pass Through Queries can be much quicker if you want to select data.

 

[quatermass]

Mark,

Is your ODBC Data Source Name(DSN) a system or user variety?

Following on from fredk, a System DSN will allow anybody using the machine access to the ODBC connection, whereas a User DSN will be limited to the user profile of the person who set up the DSN.

If a DSN is of 'user' type, errors will occur is somebody else tries to use it.

As a rule, I always set up System DSNs, as I never know which users will use the machines.

Hope it is of some help

Kevin


**************************************************************
The difference between fiction and reality is that fiction has to make sense.
**************************************************************

 

[MPBCHM]

Hey Kevin,

My ODBC's are setup as a "User" in the Machine Data Source" tab.
No other users are connecting to the database I am using.

We are currently running two databases.

A "Production Database" and a "Report Server Database"

All users are connect to the Production database where data entry is done.

The report server is a restored backup snapshot of the Production database.

I don't have the problem of user traffic to contend with in this case.

I used the "Create table Wizard" to setup the link. Could there be a bug in Access 2000 that would cause the ODBC link to drop when the program (Access)is closed?

My ODBC drivers are setup correctly as they work fine in all my other programs. Its just in Access that I will lose my connection after a second attempt to connect to the newly created linked tables.

I hope this helps you better understand my situation...

Thanks Kevin

Mark

 

[KyleS]

Mark,
Have you tried creating the ODBC data source outside of access and using it to link to the backend?

This would answer your question about this bieng an Access Wizard bug... Kyle :

 

[jfischer]

Mark,
ODBC features are dependent on the software provider. I'm not personally familiar with SQLBase, but what seems to be happening is that your login to SQLBase is not automated. Either the vendor does not support automated login using their ODBC driver, or they do not store the login settings needed to automated the process, or your application does not pass the necessary login information to the SQLBase ODBC driver. Some vendors of other SQL products I've used force you to use their login screen with no way around it. You might try writing some VBA code to do your own automated login. You'll need the vendor's documentation to tell you the parameters and arguments that need to be passed to their ODBC driver. For example, here is a connection string for Microsoft SQL Server that would do an automatic login:

sConnStr = "PROVIDER=SQLOLEDB;PERSIST SECURITY INFO=TRUE;USER ID=sa;PASSWORD=password;INITIAL CATALOG=MySQLDatabase;DATA SOURCE=MyServerName"

 

[xbasslichtie]

I have had to use ODBC to link an access table to a word document as DDE wasnt working right. The problem I have is that when I merge the data, Word chops off any characters over the 255 mark, is there any way round this?

 

[FancyPrairie]

I have found that it is much better to use a File DSN rather than a System or User DSN. That way I don't have to setup the DSN on every machine I put my database on. Note that the File DSN should reside on a server that everyone has access to. You set up a File DSN the same way you set up a System or User DSN.