Question Locked

ODBC Connections

0 helpful
3 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Hi,

Im attempting to connect to an Access data base on a server (running server 2003), from VBscripts run on the work stations.

It is my ultimate goal for the scripts to write to the database on the server, where the database is hidden and inaccesable by a non-admin user.

I have set up the ODBC souce on the server as a System DSN pointing to my database, and can access it fine on the server.

However when I run the scrips on other workstations they cannnot find the DNS.

Do I need to set up a connection from each workstation? and if so is there a way to advoid this?

The very small portion of code i'm using so far is :

Dim Connection
dim str

str = "UMdb"
Set Connection = CreateObject("ADODB.Connection")
Connection.Open str

3 replies

by cconant

We run Crystal Reports and at every workstation we have to set up the ODBC connections. I'm not sure if there is any way around it.

by tony hopkinson

the DSN on every machine.

DSN's a very old hat, I personally wouldn't recommend using them at all.

However that's by the by, what user is going to be running the script, it can't be the logon user, you don't want them to have the privilege.

Personally I'd set up a SQL server (you could use the free express version).

You can point access at it, so any forms etc you have will work if you keep teh table structures the same.

More importantly you could create a stored procedure which does the data write to the table(s), give the workstation users execute and reference privileges. Then the only thing they can do is effectively logon again to have any impact on the data you are collecting.
The client script would then collect it's data and call the SP with whatever you were interested in as parameters.
Integrated security would take care of the rest.
Bit more learning perhaps, but a much more robust aand extensible solution.

This discussion is closed to new replies.