Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

ODBC connection to Access then supply password for Timberline database

Posted on 2008-10-15
8
Medium Priority
?
942 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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 500 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

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

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

In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

885 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