Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

ODBC connection to Access then supply password for Timberline database

Posted on 2008-10-15
8
Medium Priority
?
939 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
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
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…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

670 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