We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

How to use ODBC between 2 databases.

Jillbo
Jillbo asked
on
Medium Priority
655 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
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2016
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Author

Commented:
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

Author

Commented:
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
CERTIFIED EXPERT
Top Expert 2016

Commented:
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.

Author

Commented:
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
CERTIFIED EXPERT
Top Expert 2016

Commented:
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.

Author

Commented:
I will do but I need to ubderstands odbc and pass through queries first.
CERTIFIED EXPERT
Top Expert 2016

Commented:
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.

Commented:
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)

Author

Commented:
Thanks guys,
I am working through it all at the moment. i will get back to you shortly.
Cheers
Jill

Author

Commented:
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?

Author

Commented:
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
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Author

Commented:
Hi guys,
Split the points as both of you helped me out. Sorry it took so long to finalise.
Cheers
Jill

Commented:
Glad we could help, success with the application !

Nic;o)
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.