How to use ODBC between 2 databases.

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

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

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.

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

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.

I will do but I need to ubderstands odbc and pass through queries first.

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.

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)

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

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?

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

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

Glad we could help, success with the application !

Nic;o)