Solved

ODBC connection to Access then supply password for Timberline database

Posted on 2008-10-15
8
900 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
  • 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
 
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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

This article discusses four methods for overlaying images in a container on a web page
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

919 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now