Solved

ODBC connection to Access then supply password for Timberline database

Posted on 2008-10-15
8
929 Views
Last Modified: 2013-12-20
Hi,

I have successfully connected to an Access database and queried the Access tables with the ODBC below.  However, I need to query Sage Timberline tables that I have linked to in the Access database.  The linked Timberline tables require a user/password combo once the ODBC connection to the Access database has been established.  For example when viewing the database through MS Access, when I double click a linked Timberline table to view it I have to supply my Timberline user name and password.  How can I supply this info in my PHP ODBC connection file?  

Thanks in advance.
<?php
 
$dsn="PlantSearch";
$user="";
$upasswd="";
 
$conn=odbc_connect($dsn,$user,$upasswd  ) or die();
 
echo "connected";
 
?>

Open in new window

0
Comment
Question by:foxymoron7
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
8 Comments
 
LVL 18

Expert Comment

by:mirtheil
ID: 22726629
You would supply the username/password in the $user and $upasswd variables in your PHP script.  
 
0
 
LVL 1

Author Comment

by:foxymoron7
ID: 22726708
Not the user and password for the database/dsn.  There is a secondary username and password needed specifically for the Timberline linked tables.

Thanks.
0
 
LVL 18

Expert Comment

by:mirtheil
ID: 22727123
Just out of curiosity, why are you linking the Pervasive tables in the Access Database?  Why not just go straight to the Pervasive (Timberline) tables?  

I'm not sure if there's a way to pass two sets of credentials (one for the DSN and one for the Linked Tables).  
0
 Watch the Recording: Learning MySQL 5.7

MySQL 5.7 has a lot of new features. If you've dabbled with an older version of MySQL, it is definitely worth learning.

 
LVL 1

Author Comment

by:foxymoron7
ID: 22727233
I'm running queries that pull from tables in both databases in the same query.  I assumed that would be easier than trying to get results from two separate databases in the same query.  Is that a bad assumption?  Sorry, for my ignorance but I'm still pretty new to all of this.
0
 
LVL 28

Accepted Solution

by:
Bill Bach earned 125 total points
ID: 22752849
Here's a better solution to stick with the Linked Tables (if you REALLY want to do this):

In MSAccess, create a new query. Change the view to "SQL" view and enter the statement "SELECT * FROM <tablename>;" where <tablename> is the name of your Timberline table (in PSQL).  Then, change the query type to a SQL-Specific/SQL Pass-through query.  Save the query.

Double-click on the query, and you'll be prompted for DSN, then username and password.  Do this as a test, but note down the fields that you provide and make sure that it works.  (When the query opens, you should see data.)

Change the mode back from data to SQL again, and you'll see the SQL statement.  Select View/Properties and you'll find the ODBC Connect Str item, which should simply show "ODBC;".  Click the "..." button next to the element, then select the DSN and enter your username and password there.  It should automatically create a line of the form "DSN=name;UID=user;PWD=pass;", with some other stuff in it.  

If you have many tables to do this with, select the text in the ODBC Connect Str line and you can simply paste that back into the other ODBC connection lines for the other queries.
0
 
LVL 1

Author Comment

by:foxymoron7
ID: 22760317
Thanks, BillBach.  I'm going to try this now and I'll reply later with my results.
0
 
LVL 1

Author Closing Comment

by:foxymoron7
ID: 31506506
Thanks, BillBach.  Based on your response, it seems there may be a better way to accomplish what I want.  Can you tell me more about it?

Thanks again.
0
 
LVL 28

Expert Comment

by:Bill Bach
ID: 22800459
The question is already closed, making additional commentary a bit more troublesome.  

If you have a Pervasive database engine at your disposal, I guess I find it hard to figure out why you ALSO have some MSAccess tables.  You should be able to directly query the PSQL tables and leave Access out of the loop -- and it'll probably be a bit faster, too.  

If you have additional tables that you need to link with (like product codes, color lists, etc.), then I can understand why you might need extra tables.  However, you have a full-fledged database that you can use for this.  Create a new database in the Pervasive Control Center (I hope this ships with Timberline) and simply store your data in there.  To join your tables with Timberline, then, you only need to know the "database name".  (This is how PSQL knows your database.)  

Once you've found the name, you can access tables in various databases by adding the name to the front of each table, like this:
    SELECT * FROM Demodata.Person
This query will extract data from the Person table in the Pervasive DEMODATA database -- regardless of which database you are in.  Of course, you can join tables or do any other SQL queries across databases, as well.
0

Featured Post

Don't Cry: How Liquid Web is Ensuring Security

WannaCry is just the start. Read how Liquid Web is protecting itself and its customers against new threats.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
Originally, this post was published on Monitis Blog, you can check it here . In business circles, we sometimes hear that today is the “age of the customer.” And so it is. Thanks to the enormous advances over the past few years in consumer techno…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

615 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question