Connect to network database using IP (Access2000)

Topic

New Reply

Is it possible to connect to an Access database on our network using its IP address. I want to open a recordset on that db
to to check for the latest version when another database is opened.
Thanks

Reply | Quote

Replies

Clarify please. Do you want to connect to an .mdb on your network from a location outside of your network (on the internet)? Or do you just want to connect to another database on your network? Or, do you want to connect to a database using a particular machine?

Now I’m confused. Please explain a little better.

Reply | Quote

Drew,
I want to connect through our companies intranet. There is a website on one of the servers that anyone in the company can access.
On that server is a Access database that I want to be able to connect to from anywhere in the company through IP.
What this is for is I’m going to create a table in that database with version information for several company databases that I have created.
When they open their database I want it to connect to the db on the server, open a recordset with the version information to see if they are running the latest.
I have complete access to the server, the website and the database.
I already have a routine to see if they are connected to the lan before it would even run the version check.
Thanks for any assistance

Reply | Quote

From what you described, you just need to link a table to that database.

You can use ADO also. For example:

Dim cnn as ADODB.Connection
Dim rs as ADODB.Recordset
Dim strSQL as String
Set cnn=new ADODB.Connection
With .cnn
.Provider=”Microsoft.Jet.OLEDB.4.0″
.Open “MyWebServerMyWebServerShareMyDatabase.mdb”
End With
Set rs=New ADODB.Recordset
strSQL=”Select * ” & _
“From MyVersionTable;”
rs.Open strSQL, cnn, adOpenKeyset, adLockReadOnly

….

rs.close
set rs=Nothing
cnn.Close
set cnn=Nothing

Hope that helps!

Reply | Quote

Drew,
I’m getting a message about not a valid path. The database is in a folder(updates) in the wwwroot folder on the server.
There is a DNS entry pointing to server so anyone in the company can access it by name(dealer).
I have also been playing with ASP and I can open a recordset from that database(remote) on an ASP page but can figure out how to open that recordset in my local database.
Do you have any other suggestions?
Thanks

Reply | Quote

You’re almost there. You need to SHARE the folder that the database is in.

So you would have:

dealerMyShareNameMyDB.mdb

(You can create a linked table for your local .mdbs too.)

Reply | Quote

Thanks Drew that did it

Reply | Quote

No problem. Glad to help!

Reply | Quote

Drew,
I ran into another problem. When I tried to run the database from another pc on the network I couldn’t connect to the remote db.
I’m a user on my pc and the remote so that is why it works from mine.
The remote db is on a windows 2000 workstation.
If I navigate through the network neighborhood on a win2k pc that is trying to connect to the remote db, it is asking for a “connect as” and a “password”.
Do I need to put a used and password somewhere in the connection string?
Thanks

Reply | Quote

Are you saying that the remote database is not on a server that everyone can access, it’s on another workstation with a drive that has been shared to your network? The login it’s asking for is a user login for that machine that will grant permissions to connect to the other database. You might want to set up a profile on the other machine that contains only the necessary permissions for the database folder so that you don’t compromise the workstation’s security when you connect to the database.

Reply | Quote

I think you do, and the problem is it will need to be different for each user. Unfortunately, I don’t know the syntax you are going to need – maybe someone else will.

Just out of curiosity, what’s going to happen when someone doesn’t have the latest version? If you are trying to push out a new version, there is a commercial version available from FMS that handles most of the issues very nicely. (We also have a similar product called DBLauncher that we use at client sites that we may commercialize at some point in the future.)

Reply | Quote

Charlotte/Wendell,
I have secured the workstation by setting permissions on just the folder where the database is to read only. Plus it is on the company intranet and only going to be used by company employees.
I created a user on the workstation that has access to that shared folder where the remote db is.
From what I can see in the connection string syntax the user and password strings are for the actual database(if password proected) not the connection to the workstation.

Reply | Quote

Correct. You will need to setup the permissions on the share, and on the folder, to let the people have access that you want to have access. Actually, the recommended method is to set the share wide open (give ‘Everyone’ all permissions), and then set the restrictive permissions on the folder/files, with NTFS permissions.

Reply | Quote

Drew,
Where does the login and password go to actually connect to the workstation when the database is being accessed?

Reply | Quote

You shouldn’t have to put one anywhere. If the users are NOT logging onto their workstations, there is the problem. Are you running a domain, or are you running a peer to peer network?

If you are running a domain, then you need to have people log onto the network, then the credentials are passed by the OS, and you don’t have to do anything else.

If you are running Peer to Peer, then you need to map a drive to the share, and have the mapping save the credentials.

Reply | Quote

Mission accomplished.
Found bits and pieces on the web and modified them for what I was trying to do.
In the zip file are 2 databases and 1 .asp file.
The versions.mdb and the getdata.asp are placed on the website, intranet…etc
The connect.mdb has a form with a command button that connects to the url where the .asp and mdb are located.
It then opens up a recordset from the GetData.asp page and compares it with a table in the local db that has version information in it.
Then pops up a message about the new version.
I’m using this in the OnOpen event of the main form of my production databases. Works Great
Thanks to all,
Scott

Reply | Quote