Click here to Skip to main content
15,030,781 members
Please Sign up or sign in to vote.
5.00/5 (1 vote)
See more:
Hi
I want to connect to a Remote MS Access Database which is located on a remote machine. I was thinking of doing it either with the ip address (\\xxx.xxx.xxx.xxx\mydb\acc.mdb) or through the machine name (like \\mymachine\mydb\acc.mdb)

Can anyone please guide me as how we can do this and help me with the string required to connect to a remote database?

--
AJ
Posted

VB
Dim cn As Object
Dim rst As Object
Dim strConnection As String
Dim strSql As String

Set cn = CreateObject("ADODB.Connection")
strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & "path of the file" & "Data.mdb"
strSql = "SELECT * FROM User_Login where User_Id=’username';"
cn.Open strConnection
Set rst = cn.Execute(strSql)

If rst.BOF And rst.EOF Then
MsgBox "No data found"
Exit Sub
Else
uname1 = Trim(rst.Fields(0))
pwd1 = Trim(rst.Fields(1))
End If

rst.Close
Set rst = Nothing
cn.Close
Set cn = Nothing


refer this for more details
   
Comments
CHill60 22-Dec-14 9:41am
   
Question is 3 years old and resolved.
Hi all

I found a solution for this thing,

You got to put your database in a shared folder e.g: C:\SharedFolder\database.mdb where your "SharedFolder" is shared.

Now supposed the ip address of your computer is 192.168.1.22 then you can access it from VB6 using: -

Public cn As ADODB.Connection
Public rs As ADODB.Recordset

Public Function DBConnect()

On Error GoTo openErr

'Dim cn As ADODB.Connection
'Dim rs As ADODB.Recordset

Dim MSDatabase As String

'MSDatabase = App.Path & "\" & "database.mdb" 'This works

MSDatabase = "\\192.168.1.22\SharedFolder\database.mdb"

Set cn = New ADODB.Connection

cn.CursorLocation = adUseClient
cn.Provider = "Microsoft.Jet.OLEDB.4.0"
cn.Open MSDatabase, Admin
MsgBox "Database connected"
Exit Function
openErr:
MsgBox "Databae not connected"
End Function


Hope this helps everyone who are facing the same problem

Chers!!!

--
AJ
   
Comments
evry1falls 18-Nov-13 17:06pm
   
I know this solution, I've read it before on 2 different websites and it worked for me ... But using shared folder to connect to a database will not be the best solution with Multi-users connect at the same time. Since there is an offline mode in ado.net [DataSet], it made it more efficient to work with multi-users. I'm using this solution along with Ado.Net in my work, and is working like a charm.
Here is the source I've read this at [http://vb6access2003.blogspot.com/2010/07/vb-60-ms-access-2003-ms-access-2003.html]
First, if you have a choice, don't use VB6 which is a legacy environment. So if possible shift to VB.NET.

Secondly, Access is basically just a flat file so what you need is access to the file itself. You can map the share using both IP or UNC or use the file without mapping.

Thirdly, you didn't mention the technology you want to use. If you are really required to use VB6, I think ADO would be the logical choice. Here's one tutorial how to connect to an Access database with ODBC using ADO: http://www.vb6.us/tutorials/database-access-ado-vb6-tutorial[^]
   
Comments
ankitjoshi24 4-Apr-11 15:57pm
   
Hey

Thanks for the reply. I could get through the link you have sent.

And I will be using ADO connectivity.

I will try using the solution provided in the link and will let you know if this works.

Also if you have any other solution to this problem, please let me know. It will be an extra help.

--
AJ
ankitjoshi24 4-Apr-11 15:59pm
   
Just one question I had,

Does this help my client to connect to the database which is on the server machine???

I want my client to connect remotely to the database store on my server machine. And the client would not be working on the same machine as the server.

--
AJ
Wendelius 4-Apr-11 16:11pm
   
Yes it does. Access is basically just a file, not a full-blooded database server. You can compare Access to an Excel file. As long as you have the privileges to see and open the file for editing, you can connect to it using for example an ODBC driver (and ADO).
   
#1: don't use VB, #2: don't use Access...
Mika, the Answer is adequate, my 5.
--SA
ankitjoshi24 5-Apr-11 16:23pm
   
BTW, did anyone try my solution which I have posted??? and does anyone have a better way of doing it???
Wendelius 5-Apr-11 16:33pm
   
Probably not. When you post a solution only the person who created the question gets notified so in this case, you. Looks like a working solution as long as the folder is accessible from the other computer or if you use names, DNS is available.
ankitjoshi24 6-Apr-11 10:11am
   
Mmmm hmmm

the folder has to be accessible and yes I tried the DNS thing, but I could not make it work. Not sure what the problem was but my program just could not find the database. So I went through this, which is not that good but it's a good enough program.
Wendelius 6-Apr-11 15:29pm
   
If the names weren't working it's propably because you don't have a common name resolving (DNS) to use so the computer name cannot be interpreted as IP address.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900