Solved

ODBC connection to Access then supply password for Timberline database

Posted on 2008-10-15
8
919 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Many old projects have bad code, but the budget doesn't exist to rewrite the codebase. You can update this code to be safer by introducing contemporary input validation, sanitation, and safer database queries.
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

756 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