Connect to ODBC database linked tables in Access database

cteakle
cteakle used Ask the Experts™
on
Hi
I am using microsoft access 2003 to write a front end to an sqlbase database.  The tables from the sqlbase database have been linked into the database using an ODBC connection.  This all works fine when I make the connections.  However, when I reopen the database, if I want to use one of the tables, I have to fill in the same dialog box that I have to when I connect the first time.
 screenshot of dialog box I would like to be able to bypass this and have that open when I open the first form of the database application.
I am sure I will need to provide some extra information - but not sure what else at this stage.

I am also not sure about the difficulty of this question - to me, if it is hard enough to submit here then it is hard - all relative to your knowledge base I know. A solution would be very worthwhile to  me - hence maximum points.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Did you set up a DSN under the ODBC Data Source Administrator? (In Administrative tools) If so, dis you set it up as File, User, or System?

Here is a good reference on how to set it up:
http://www.unify.com/onlinedocs/sb/books/connecting/connecting_odbc_intro.htm
Top Expert 2006

Commented:
Was there any prompt to save the password? If so try that but best make sure its a user dsn.

Did you link the tables into MSAccess? Did you enter the password there?

Author

Commented:
Hi Sumerdai.  I will check out the reference.  In answer to your question, the DSN was set up as a System DSN.
screenshot2.JPG
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
I linked the tables in Access and didnt have to enter any password, just hit OK in the top dialog box. The ODBC was set up by our IT guy who does the admistration for the GUPTA database.

Author

Commented:
Hi Rockiroads - I will try and set it up as a USER DSN

Author

Commented:
I - I set up a USER DSN with the same parameters as the SYSTEM - same thing.  Opens fine first time, but needs dialog box second time.
Top Expert 2006

Commented:
doesnt matter about the userdsn. I only said that cos of the password thing.

when you link the tables there should be no need to prompt.

on the linked tables list, if you right click on the linked table, select linked table manager. click on one table and check always prompt for new location. see if that refresh helps

Commented:
It doesn't look like the Username and password are saved in the DSN. If you hit "Test Connect" does it prompt you for a password?
Commented:
Are you running SQLBase Client version 6.1.02 or below? If so you may need to upgrade the client - it looks like there is a known issue:

http://www.tek-tips.com/faqs.cfm?fid=3486

Author

Commented:
Hi rockiroads
I got the same dialog box when I did the refresh.
I realise that I need to add a bit more information.  This dialog box is no problem generally.  However, the application that I am building links to a table of contacts from the sqlbase database.  It uses Ostrosoft to read mail from a mail server - then goes to the database of contacts, checks to see which company the contact belongs to then writes a record in the database with information from the email and sends a message on to the accounting section to let them know that a message has arrived.  You dont need any more background regarding the application and the why's of it.  We want to run the access application as a service ( i dont know much about these - the IT man is organising this).  The application will close and restart at a preset time in the early hours of the morning.  We cant have the application hanging,waiting for a response to the dialog box before it goes ahead and runs the remainder of the code.

Author

Commented:
Thanks again for your help sumerdai and rockiroads.  I will go and do some more research in the links that each of you has provided and see how I get on.
CT
Top Expert 2006

Commented:
using access as a service, thats something new. wow. I assume you mean a windows service? some background process? If so then I would of thought it would be better being written in a proper development tool. VB.Net Express is free and may do what you want.

Regardless. What if you just used ado to define your connection and recordsets and check it that way instead of using linked tables. You can still reference your odbc or if mdb available, use that full path.

Author

Commented:
Thanks Rockiroads - I will try the ADO thing and let you know how I go.  Our application is a bit of a "House that Jack Built"  We may end up rewriting it some day.  I will look at the VB.Net Express too.
Top Expert 2006

Commented:
you should be able to write up some module code so do away with linked tables. if that works then should be easy enough to convert to vb.net express. I know you can create services with dotnet, not 100% sure if you can with vb.net express.

Commented:
You can run access as a scheduled task, you just need to get the connection saved properly.  

Author

Commented:
Hi
I talked to the IT manager.  He installed SQLBase Client version 11.05 and my problem has disappeared. We did have 9.05 installed previously.  So I will award points to Sumerdai.
Thanks all for your help

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial