SQL Server compatibility

[Tapp]

I am building an Access 97 Database,
The data I want is on SQL Server
An SQL Query is run to extract the data I want
The data is output to excel, I edit to suit and upload to Access

I want to be able to have a query in Access that will get the data directly from SQL server.

I copied the SQL code and pasted it in to Access Query but it did not work


What do I need to do to get this to work?

Thanks

 

[Tapp]

Sorry for the double post
accident!

 

[ChiTownDiva]

Hi, Tapp...

I believe you have to write a Pass-Through Query to go directly to the server to pull in the data.

First, open your Pass-Through , I believe you use something like "ODBC;DSN=Human resources;SERVER=HRSRVR;UID=Smith;PWD=Sesame"

Then I would write a macro to run the Pass-through then the regular Access query to manipulate your data.

Or you could just connect directly by going to the database through an ODBC.

 

[ChiTownDiva]

Sorry...

I meant, you could connect by linking your tables through and ODBC.

ChiTownDiva

 

[Tapp]

Thanks ChiTownDiva

I'm not really sure about First, Pass-Through but I will give it a good go
I dont have access to the sequal server but dohavethecode that was used to get the data
I will work at it till it works
Thanks

 

[Tapp]

Thanks ChiTownDiva

I'm not really sure about First, Pass-Through but I will give it a good go
I dont have access to the sequal server but dohavethecode that was used to get the data
I will work at it till it works
Thanks

 

[ChiTownDiva]

The code used in SQL Server won't work in Access. Access uses a slightly different SQL than SQL Server.

Try doing an outside database connection and running the queries from there.

ChiTownDiva