Connect to an online database

[GMcFly]

Hello everybody. I'm trying to connect to a databse which is online. I tried the following code, but it doesn't work. Does anybody know the answer? Is it even possible to connect to an online database?

code:

Option Compare Database

Private strSQL As String
Private cnnData As New ADODB.Connection
Private rstData As ADODB.Recordset

Private Sub Form_Load()

With cnnData
.ConnectionString = "Driver={Microsoft Access Driver (*.mdb)};" & _
"Dbq=ftp://server/database.mdb;" & _
"Uid=xxxxxx;Pwd=xxxxxx"
.Open
End With

strSQL = "SELECT * FROM tblMembers"
With rstData
Set .ActiveConnection = cnnData
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Open strSQL
End With

Set Me.Recordset = rstData
Me.txLName.ControlSource = "tx_LName"
End Sub


Thankx

[GMcFly]

Ok, I tried this link: http://www.able-consulting.com/ADO_Conn.htm (I saw it on a lot of threads ). I came to the conclusion that I need to connect to a remote machine using MS Remote or RDS since I don't want to use ASP. This is what (I guess) my code should look like. What I don't understand is that the database isn't specified anywhere??

oConn.Open "Provider=MS Remote;" & _
"Remote Server=http://myServerName;" & _
"Remote Provider=MSDASQL;" & _
"DSN=AdvWorks;" & _
"Uid=myUsername;" & _
"Pwd=myPassword;"

I'm not a champ in VB so if somebody could please help

[Ecniv]

"Remote Provider=MSDASQL;" & _
This specifies Sql server

"DSN=AdvWorks;" & _
This specifies a DSN which usually contains the database you wanna connect to


Vince

[GMcFly]

Probably a stupid question, but what is DSN?
I also have another problem. I just have an .mdb stored on the server but there is no SQL server.
What I actually want to do, is split a databse in two, put the tables online so people can have the forms on different locations. Any ideas

[mendhak]

Originally posted by GMcFly
Probably a stupid question, but what is DSN?
I also have another problem. I just have an .mdb stored on the server but there is no SQL server.
What I actually want to do, is split a databse in two, put the tables online so people can have the forms on different locations. Any ideas

DSN: it's a method used to connect to dbs.

You have an mdb file, which is an ACCESS file. SQL Server is not related to this. You do not need to worry about SQL Server here. What exactly are you trying for?

If you'd like to place your database online, you can get your connection string from that link you have, for ado connection strings.

If your questions are still unanswered, please post back and be a little clearer.

[mendhak]

Maybe I could have defined DSN better

Here's a copy and paste:

Short for Data Source Name. Data Source Name provides connectivity to a database through an ODBC driver. The DSN contains database name, directory, database driver, UserID, password, and other information. Once you create a DSN for a particular database, you can use the DSN in an application to call information from the database.

There are three types of DSNs:
(1) System DSN -- can be used by anyone who has access to the machine. DSN info is stored in the registry.
(2) User DSN -- created for a specific user. Also stored in the registry.
(3) File DSN -- DSN info is stored in a text file with .DSN extension.

DSN is often used by Active Server Pages (ASP) and Visual Basic programs when a query to a database is necessary to retrieve information.

There is also what is known as a "DSN-less connection." Instead of using a DSN to connect to a database, the developer specifies the necessary information right in the application. With a DSN-less connection the developer is free to use connection standards other than ODBC, such as OLE DB.

[GMcFly]

Thanks very much . Now I know what DSN is.
I'll try to explain what I'm trying to do. There are 2 different databases. One contains forms & reports and is on the user's computer. The other database only contains tables and is online. If there is a way to link these 2 databases, the one with the forms can be used on different computers.
That's all I want to do.

Should the code look like this?

oConn.Open "Provider=MS Remote;" & _
"Remote Server=http://myServerName;" & _
"DSN=???;" & _
"Uid=myUsername;" & _
"Pwd=myPassword;"

???: still not sure of what I have to fill in

Thanks

[GMcFly]

I found out how to make a system DSN!! When I click on add in the "System DSN" tab; I select the Access driver; I select a database. That is where my problem is: I can't select an online database! I tried going into "Network" and selecting an ftp shortcut I have in my "My Network Places" but I can't select any folders
One other thing, i know the ip address of the server, could this help??
Does anybody know what I should do?

[mendhak]

I myself don't use DSN. I use DSN less.

but if you have an option there where you can specify the IP address, then DO that.

maybe something like

121.92.100.4/something/whatever.mdb

[GMcFly]

Hello, me again
I tried something completely different:

Private Sub button_Click()
DoCmd.TransferDatabase acLink, "Microsoft Access", _
"\\ip.adr.es.s\tables.mdb", acTable, "tblMembers", _
"tblMembers"
End Sub

Could this work?
I tried this but it doesn't work. I'm starting to wonder if I have the correct ip adress

[mendhak]

What makes you think that?

and... were you unable to find the proper connection string in that link?

I do not know what you have just posted. sorry.

[GMcFly]

Sorry, I didn't really get what you didn't understand in my last reply

[mendhak]

DoCmd.TransferDatabase acLink, "Microsoft Access", _
"\\ip.adr.es.s\tables.mdb", acTable, "tblMembers", _
"tblMembers"
End Sub

[GMcFly]

First of all a little english mistake I made "address" (and not adress)

I looked in the VB help and found the "TransferDatabase Method" they gave this exemple:
DoCmd.TransferDatabase acImport, "Microsoft Access", _
"C:\My Documents\NWSales.mdb", acReport, "NW Sales for April", _
"Corporate Sales for April"

I thought this might also work for an online databse. You were talking about

were you unable to find the proper connection string in that link

?

[mendhak]

The link i'm referring to is
http://www.able-consulting.com/ado_conn.htm

I don't know that method you've referred to... so.. it seems I'm unable to help you due to some sort of a confusion. I apologize for that. (btw, spelling don't matter!) (I don't work with DSN)

I suggest you click on New topic, and once again post the question from where you've left off. This way you can get more responses.

Good luck.

[Ecniv]

McFly... (hover boards coming??)

Are you just linking the tables from one mdb to another. Just that one mdb is online instead of on a network server/hd ?


The Docmd thing is a built in function of Access, which means you'd need the access references in there.

How are you connecting atm ?
- is it a linked table (surely thats so slow...) ? Can you link through the get external data ?


oConn.Open "Provider=MS Remote;Remote Server=http://myServerName;DSN=???;Uid=myUsername;Pwd=myPassword;"

I haven't used the remote connections, but perhaps RDO is better here than ADO ?? Worth a look ?
If the DSN can be saved from your computer, and yet still be flexible for other users this might be one way.

Go to the microsoft site, find a support email addy and send them the question - see what they recommend ?

If you have time, go to a book store or library n look up the RDO/remote connections/Net databases/Net programming... might have some use ful pointers there.

Good luck

Vince

[GMcFly]

Yep, hover boards coming!! Back to the Future

Thanks for your advice, both of you.

CU

[mendhak]

YW.