Remote ADP connection to MSDE

[GAVO-UK]

Hello everyone.

Using Access 2003 and MSDE

I just started experimenting using Access as the front end for SQL
using *.adp, and would like to connect the front end to the back end
remotely, purely through the internet to a remote computer, hosting the
SQL Server (MSDE), but the problem is that I don't know where to
start, the only thing I do know is that I have to create a connection
string using ADO - OLE DB, something like this

oConn.Open "Provider=sqloledb;" & _
"Network Library=DBMSSOCN;" & _
"Data Source=xxx.xxx.xxx.xxx,1433;" & _
"Initial Catalog=myDatabaseName;" & _
"User ID=myUsername;" & _
"Password=myPassword"

found at:
http://www.carlprothman.net/Default....erForSQLServer

The SQL Server would be hosted at my computer running Windows XP (Home
ed). Ideally I would like to distribute the front the to the client
computer and automate the connection to the remote server.

My questions;

What file should I distribute to the client computer, is it the *.adp I
am working???

Where do I place the connection string, and what other things should I
take into account to create a successful connection between the front
end and the back end. I have already enabled TCP/IP for the MSDE on
port 1433

Any comments would be appreciated and any direction to on-line
tutorials where I can learn this sort of things would help me a lot.

Thanks
GAVO.

[Steve]

Code such as the connection string you posted must be entered in the
Visual Basic Environment and accompanied by code which instantiates the
objects you're using (in this case, oConn must be a Connection object).
You can specify a form for your database to load when it opens, and in
the Form_Load event of that form enter your connection code. For
example, your Form_Load would look like this:

Dim tbl as ADOX.Table
Dim cat as New ADOX.Catalog

Set tbl=New ADOX.Table
tbl.NAME="tblNameToCallLinkedTable"
tbl.ParentCatalog=cat

tbl.Properties("Jet OLEDB:Create Link")=True
tbl.Properties("Jet OLEDB:Link
Datasource")="MyDatasourcePathOrOtherConnection"
tbl.Properties("Jet OLEDB:Link Provider String")="This is where you put
your connection string"
tbl.Properties("Jet OLEDB:Remote Table Name")="tblNameOfTblToLinkTo"

cat.Tables.Append(tbl)

This code links a table from a remote database (your back end). You can
then use the table in the local database with queries, forms, etc.

[GAVO-UK]

Thank you steve.

Do I have to do the connection for every form I open, or is there a way
to create a connection for all the forms, reports, etc...

Is the way you showed me appropiate for a multiuser enviroment, over
the internet?

Thanks
GAVO.

[Br]

GAVO-UK wrote:

Hello everyone.

Using Access 2003 and MSDE

I just started experimenting using Access as the front end for SQL
using *.adp, and would like to connect the front end to the back end
remotely, purely through the internet to a remote computer, hosting
the SQL Server (MSDE), but the problem is that I don't know where to
start, the only thing I do know is that I have to create a connection
string using ADO - OLE DB, something like this

oConn.Open "Provider=sqloledb;" & _
"Network Library=DBMSSOCN;" & _
"Data Source=xxx.xxx.xxx.xxx,1433;" & _
"Initial Catalog=myDatabaseName;" & _
"User ID=myUsername;" & _
"Password=myPassword"

found at:
http://www.carlprothman.net/Default....erForSQLServer

The SQL Server would be hosted at my computer running Windows XP (Home
ed). Ideally I would like to distribute the front the to the client
computer and automate the connection to the remote server.

My questions;

What file should I distribute to the client computer, is it the *.adp
I am working???

Where do I place the connection string, and what other things should I
take into account to create a successful connection between the front
end and the back end. I have already enabled TCP/IP for the MSDE on
port 1433

Any comments would be appreciated and any direction to on-line
tutorials where I can learn this sort of things would help me a lot.

Thanks
GAVO.

Yes, you'd have to distribute the ADP.

Why not just use the File/Connection menu dialog to setup your
connection?
--
regards,

Bradley

A Christian Response
http://www.pastornet.net.au/response

[GAVO-UK]

Steve wrote:

Code such as the connection string you posted must be entered in the
Visual Basic Environment and accompanied by code which instantiates the
objects you're using (in this case, oConn must be a Connection object).
You can specify a form for your database to load when it opens, and in
the Form_Load event of that form enter your connection code. For
example, your Form_Load would look like this:

Dim tbl as ADOX.Table
Dim cat as New ADOX.Catalog

Set tbl=New ADOX.Table
tbl.NAME="tblNameToCallLinkedTable"
tbl.ParentCatalog=cat

tbl.Properties("Jet OLEDB:Create Link")=True
tbl.Properties("Jet OLEDB:Link
Datasource")="MyDatasourcePathOrOtherConnection"
tbl.Properties("Jet OLEDB:Link Provider String")="This is where you put
your connection string"
tbl.Properties("Jet OLEDB:Remote Table Name")="tblNameOfTblToLinkTo"

cat.Tables.Append(tbl)

This code links a table from a remote database (your back end). You can
then use the table in the local database with queries, forms, etc.

Steve

For testing purposes I copied the adp to a different computer connected

to the internet on a dial up and I'm using the following code on an
"on open" even on one of the forms (unbound), but still i cannot
connect when I open the form. The funny thing is that when I run
exactly
the same adp application on the MSDE host computer it does connect
successfully.
The code is as follows;
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = New ADODB.Connection
cn.Open "Provider=sqloledb;" & _
"Network Library=DBMSSOCN;" & _
"Data Source=xxx.xxx.xxx.xxx,1433;" & _
"Initial Catalog=NorthwindCS;" & _
"User ID=sa;" & _
"Password=xxxxx"
'Create an instance of the ADO Recordset class,
'and set its properties
Set rs = New ADODB.Recordset
With rs
Set .ActiveConnection = cn
.Source = "SELECT * FROM Customers"
.LockType = adLockOptimistic
.CursorType = adOpenKeyset
.Open
End With
'Set the form's Recordset property to the ADO recordset
Set Me.Recordset = rs
Set rs = Nothing
Set cn = Nothing
I configured the firewall on the host computer to accept connection
from the remote computer by specifying its IP address and still no
luck.
Any help would be appreciated
GAVO.