Solved

How to use ODBC between 2 databases.

Posted on 2004-03-24
15
548 Views
Last Modified: 2007-12-19
I want to get to Data information held in a software package called MYOB and use it within Access. I know I need to use ODBC but I do not know how. Can anyone talk a beginner through the required steps please.
Thanks
0
Comment
Question by:Jillbo
  • 8
  • 4
  • 3
15 Comments
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 300 total points
ID: 10673027

ODBCConnectStr Property

You can use the ODBCConnectStr property in an SQL pass-through query (pass-through query: An SQL-specific query you use to send commands directly to an ODBC database server. By using pass-through queries, you work directly with the tables on the server instead of having the Microsoft Jet database engine process the data.) to specify the Open Database Connectivity (Open Database Connectivity (ODBC): A standard method of sharing data between databases and programs. ODBC drivers use the standard Structured Query Language (SQL) to gain access to external data.) (ODBC) connection string (connection string: A string expression used to open an external database.) for the query.

Note   The ODBCConnectStr property applies only to pass-through queries.

Setting
Enter the ODBC connection string that defines the connection to the SQL database (SQL database: A database based on Structured Query Language (SQL).) you want to use.

You can set this property by using the query's property sheet or Visual Basic.

You can also use the ODBC Connection String Builder (ODBC Connection String Builder: An Access tool you can use to connect to an SQL database when you create a pass-through query. If you save the query, the connection string is stored with the query.) to create the ODBC connection string for this property. This builder establishes a connection to the SQL database server and then ends the connection after the ODBC connection string is created.

Remarks
The default setting for this property is the string "ODBC;", which Microsoft Access restores if you delete an existing setting. When the ODBCConnectStr property is set to "ODBC;", Microsoft Access will prompt you for a connection string whenever the query is used but won't store the connect string. You must enter a connection string in the ODBCConnectStr property box if you want Microsoft Access to store the connection string.

Tip   If you know the full connection string for the SQL database, enter it in the ODBCConnectStr property box. This way you will avoid the need to enter the connection string in the ODBC connection dialog box each time you use the query.

The connection string is different for different types of ODBC data sources (ODBC data source: Data and the information needed to access that data from programs and databases that support the Open Database Connectivity (ODBC) protocol.). For example, to connect to the Human Resources data source on the HRSRVR server (a Microsoft SQL Server) by using the logon identification (ID) Smith and the password Sesame, you can use the following connection string.

ODBC;DSN=Human Resources;SERVER=HRSRVR;UID=Smith;PWD=Sesame;
0
 

Author Comment

by:Jillbo
ID: 10673335
Hi,
Thanks for this. I wil start working my way through it and let you know how I get on. Bear with me a while!
Cheers
Jill
0
 

Author Comment

by:Jillbo
ID: 10673341
Just a quick note though - I am not sure on how to do Pass through queries. Sorry! Can you talk me through that aswell.
Cheers
Jill
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 10673521
Select Queries on the Database window then Click New
On the New Query window select Design view
Close the Show Table window
On the Menu Bar
Select Query, On the dropdown list
Select SQL Specific then Pass-Through

A white screen window will show where you can type your query.
0
 

Author Comment

by:Jillbo
ID: 10673742
Thanks but I don't know the code to write. To summarise -
I am trying to get sales information from MYOB and bring it into Access.
Customer name, Date of sale, product sold with quantity and price.
Customer details ie name address etc

Thanks
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 10673877
I just notice, MYOB is a software package.
There should be an instruction on how to use the software or
use the Help file on the software.
0
 

Author Comment

by:Jillbo
ID: 10674152
I will do but I need to ubderstands odbc and pass through queries first.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 119

Expert Comment

by:Rey Obrero
ID: 10674317
Read this :

For example, to connect to the Human Resources data source on the HRSRVR server (a Microsoft SQL Server) by using the logon identification (ID) Smith and the password Sesame, you can use the following connection string.

ODBC;DSN=Human Resources;SERVER=HRSRVR;UID=Smith;PWD=Sesame;


 Create a pass-through query that sends commands to an SQL database

In the Database window, click Queries  under Objects, and then click New on the Database window toolbar.
In the New Query dialog box, click Design View, and then click OK.
Without adding tables or queries, click Close in the Show Table dialog box.
On the Query menu, point to SQL Specific, and then click Pass-Through.
On the toolbar, click Properties  to display the query property sheet.
In the query property sheet, set the ODBCConnectStr property to specify information on the database to which you want to connect. You can type the connection information, or click Build , and then enter information about the server you're connecting to.
When you are prompted to save the password in the connection string, select Yes if you want the password and logon stored in the connection string information.

If the query isn't the type that returns records, set the ReturnsRecords property to No.
In the SQL Pass-Through Query window, type your pass-through query.
For details on the syntax for your query, see the documentation for the SQL database server to which you're sending the query.

To run the query, click Run  on the toolbar. (For a pass-through query that returns records, you can click View  on the toolbar, instead.)
If necessary, Microsoft Access prompts you for information about your SQL server database.

0
 
LVL 54

Expert Comment

by:nico5038
ID: 10675076
You need to know the DBMS MyOB uses.
When known, you can create an ODBC definition.
Normally under file/settings/control panel you have an ODBC application or under XP try typing ODBC in the Help and use the Open ODBC Data Source Administrator link.

When the application is started define for the MyOB an ODBC System DSN pointing to the MyOB database.

Note the name and goto access.
There create a new table and chose "Link". On the fileselection change the filetype to ODBC and select the definition you created. Now you'll see all tables and will be able to select all needed ones.
The linked tables can be processed with normal Access queries, but when it's too slow try the PassThrough query described by  capricorn1.

Nic;o)
0
 

Author Comment

by:Jillbo
ID: 10683289
Thanks guys,
I am working through it all at the moment. i will get back to you shortly.
Cheers
Jill
0
 

Author Comment

by:Jillbo
ID: 10719634
Hi Nic;o
I need to go through your instructions bit by bit.
1. Open control panel and click on ODBC application.
2. Tab pages have UserDSN and SystemDSN and File DSN
3. On UserDSN MYOB is listed with Name - MYOB; Driver - MYOB ODBC
4. Not listed under System DSN or File DSN.
5. Click on MYOB Configure and options for specifying MYOB Company file and unlocking code are not enabled.

In access - I went to create a pass through query and in properties I went to build ODBCConnectStr and Select data Source comes up. In File Data source MYOB is not there. In Machine Data Source MYOB is there but when I select it I get an error message reading "Company File specified within the DSN does not exist. Reconfigure your DSN settings to gain access to the company file."

What do I need to do?
0
 

Author Comment

by:Jillbo
ID: 10720172
Hi Again,
I have managed to get Access to talk to MYOB. Yay.
I can link tables as described but I need more help on the pass through query.

I need to understand what I am trying to do and achieve. I create a new query, go to SQL specific and Pass through. In properties I build the ODBC string.
Now
Firstly - with returns records does this mean returns records to MYOB? As my data files from MYOB are read only and cannot be returned.
Secondly - Once I have built the string do I have to write all queries in SQL?
Can anyone supply any examples??? I can design queries in design view but am at a loss in SQL.
Thirdly - Do I have to write all queries in this manner?
Thanks for your help. I am slowly getting there.
Cheers
0
 
LVL 54

Assisted Solution

by:nico5038
nico5038 earned 200 total points
ID: 10721530
A passthrough query will be executed in the connected database (MyOB), so need to be in that SQL-syntax !
The passthrough query can be used to build an Access Append query to store the data locally in an Access table.

When you have sufficient access rights to the ODBC database even an update will be possible.

When you have linked tables you can issue "normal" access select queries on them, passthrough is normally used for performance reasons when you need a small selection of a table as only selected rows will be returned. A normal access query will make access to extract first all rows and perform then the WHERE.

Nic;o)
0
 

Author Comment

by:Jillbo
ID: 10864825
Hi guys,
Split the points as both of you helped me out. Sorry it took so long to finalise.
Cheers
Jill
0
 
LVL 54

Expert Comment

by:nico5038
ID: 10866860
Glad we could help, success with the application !

Nic;o)
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
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…

758 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

19 Experts available now in Live!

Get 1:1 Help Now