ODBC in VBScript

Posted on 2001-07-18
Medium Priority
Last Modified: 2006-11-17
My database(MS Access) reside on a different PC,
I can't establish the connection my PC and the PC which has my database.

I already used the ODBC with the "data source system"

Set strConn = Server.CreateObject ("ADODB.Connection")
strnConn.Open "data source=dsntestdb;user id=Admin;pwd=my_password"

But if the data source (dsntestdb) points to the MS Access file on the same PC, this code is very good.

I'd like to know how can we give the user id and password for connecting to the distant PC ?

Is my code is good ?
Question by:longlong
LVL 20

Expert Comment

ID: 6294186
In the control panel go into ODBC data sources 32bit
and create a system DSN pointing to the remote database

then for your "data source"= use the name of the system dsn you created
LVL 43

Expert Comment

ID: 6294188
When you say distant, how distant. If it is on a lan, you should be able to specify the UNC path to the database:

\\ServerName\ShareName\FolderName\DatabaseName.mdb in the dsn definition.

Author Comment

ID: 6294342
But I have to use the system data source or the file data source ? What is the difference ?
I used the system data source but it appeared the error message, you don't have the autorisation for accessing.
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.


Expert Comment

ID: 6294356
This can be done by selecting a database on the network

Just click on the network button and select the Machine,  folder and database required.

Expert Comment

ID: 6294426
BTW through ODBC you cannot use an UNC path to the file.

ODBC will map a drive for you.

You also must have asscess to the in which folder the database resides.

Go to the Machine and open explorer right click on the folder select sharing, Share As Name of folder.

Alternatively you could use a DSNless connection.

conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;data source=\\Test\Temp\Anydb.mdb;user id=Admin;pwd=my_password"

This will work with an UNC and without the need for a DSN.

The Data Source= bit should be the UNC path to your database on the network.



Expert Comment

ID: 6294543
I wouldn't recommend using a DSN-Less connection in VBScript... it would expose your UID and PWD to everyone.

conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;data source=\\Test\Temp\Anydb.mdb;user id=Admin;pwd=my_password"

If you build your DSN and hit the advanced button you can build the DSN with a User Id and password!

Author Comment

ID: 6307492
I'd like to know the password which we gave, this is the password for network (login to another PC on the network) or this is the file's password (password for open this file).

If I want to give the password for login to another PC bacause my database (Aceess) is on another PC, how can I do it ?

Accepted Solution

VincentLawlor earned 1200 total points
ID: 6307613
The password supplied to the connectio string is the password for the database.

As I said in an earlier post you will need access to the machine on which the Access database resides.

* Go to the machine on which it resides and share the folder. You will then be able to access the database from another machine.
* Map a network drive from the machine that needs to access the database. You will be asked for a username and password (Network username and password). Setup your ODBC connection as normal.
* In ODBC when you are selecting the database click the network button select the network share where the database resides (ODBC will map the drive for you). Enter the username and password (Network username and password).

If the database is password protected then in ODBC select Advanced settings and enter the username and password (Database username and password).

Hope this clears is tup for you.



Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…

597 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question