getting data from SQL Server

[NigelShaw]

Hi all

its been ages since i actually asked a question seeing as i mainly browse these days to see if i can give an answer so to change my mold a bit-

ive always worked within access tables and never had a need to connect or collect data from a sql server before so i dont actually know the best method of collecting such data.

we have a new program from my Wifes new business and its based on MS SQL Server. there is data i need to collect from the database which i cannot get from the program so a simple database connection however, i dont know any method of such. Could anyone be so kind as to guide a way through? is it a ADO connection? can i do this with a simple DAO recordset collection?

help graciously received as always



Nidge

 

[Galaxiom]

I use Pass Through queries via ODBC.

Grant user permission for the databbase in the SQL Server.
(I use Windows authentication because I am on a properly managed domain.)

Set up the ODBC DSN for the server on the client computer. (Administrative Tools)
Create a Pass Through query.
Enter a connection string in the appropriate query property.
http://www.connectionstrings.com/
Enter the SQL for your query.

 

[NigelShaw]

Hi

i created a ODBC connection using the Access link to option but can it be automated with VBA so i dont have to go through the same procedure?

regs

Nigel

 

[SOS]

If everyone has access to a particular file location, you can set up a FILE DSN which then you would not have to worry about setting up any ODBC connection on ANYONE'S machine. You connect using that for the application and as long as the users have the rights to be able to see that folder on the network, the database will work for them without having to set up a connection on their machine.

 

[NigelShaw]

Hi

I'll be dead honest, I'm not brill with Sql server. I can connect and view the database from the SQL connector software but I'm not sure which folder it is in. As I understood, you just connected to the database.

Nudge

 

[SOS]

Hi

I'll be dead honest, I'm not brill with Sql server. I can connect and view the database from the SQL connector software but I'm not sure which folder it is in. As I understood, you just connected to the database.

Nudge

You don't need to know which folder the SQL Server is installed to or anything like that. You just need to have a folder that everyone can access so that Access can read the file DSN that YOU put out there.

Perhaps this will clear up the mud.