External data link - website server to local pc

[pondlife]

Can anyone steer me to the best way to link a table in an access database hosted on a remote server to a table in an access database on a local PC?

A data access page on my website (created in FrontPage2003) is collecting names and addresses for mailings, now I need to harvest that data onto my local PC to print out the mailing labels, etc.

I'm running access 2000.

Many thanks for help/advice.

 

[Galaxiom]

Don't link. Export the data at the server and pick it up by ftp. Then import that file into your database.

 

[pondlife]

Thanks for the suggestion, Galaxiom. I am looking for a solution that automatically maintains fresh data locally for my users. Surely if I deploy the ftp solution then my users will have to manage that themselves as a manual step - in my particular case that wouldn't be an optimal solution.

I dimly remember that Acc2000 WAS capable (out of the box) of the sort of external link (/file access) to a source hosted on a remote server that I am looking for, but I also dimly remember that it is no longer supported.

Can anyone enlighten me?

 

[wazz]

create a file dsn via admin tools/data sources.

 

[pondlife]

Thanks Wazz - that's good advice too.

I think I'm going to need a touch more detail to be able to implement that - ODBC etc is new to me.

Notably, in Admin/ODBC/'Microsoft Access Setup', when I try to 'Select' my remote Access database I hit problems. The ODBC dialog requires the remote database to be on a network drive, but the 'Map Network Drive' utility won't allow the ftp path to my FrontPage server to be allocated to a network drive. Are you certain that there can be an ODBC connection to a file on a remote FrontPage website? If so, what am I doing wrong?

 

[HiTechCoach]

It is not possible to link to an Access database through a web server. You will need to use a LAN connection to the machine with the database. You have to use a UNC path or mapped drive letter (not an IP or URL) to link to an Access back end.

If you were to use an SQL server back end. It is possible to configure a port at the remote site so that you can use an IP address to link to remote a SQL server.

 

[pondlife]

ThanksHiTechCoach. I'm finding it hard to believe this is so complex/unusual. Surely everyone who collects names/addresses from a hosted FrontPage website needs to get that data back from the webserver on to a local office system. Perhaps I'm just going about it in the wrong way?

For the moment having failed to set up a WebDev web folder (FrontPage doesn't like them and not sure if Access does either), and failed to setup an ODBC connection, I'm trying to open a recordset within my local system:

Set Conn = CreateObject("ADODB.Connection")
Conn.Provider = "Microsoft.Jet.OLEDB.4.0"
Conn.Open "http://www.mysite.co.uk/fpdb/nwind.mdb"
Set RS = Conn.Execute(SQL)

But it doesn't work! Am I at least on the right track?

 

[HiTechCoach]

ThanksHiTechCoach. I'm finding it hard to believe this is so complex/unusual. Surely everyone who collects names/addresses from a hosted FrontPage website needs to get that data back from the webserver on to a local office system. Perhaps I'm just going about it in the wrong way?

For the moment having failed to set up a WebDev web folder (FrontPage doesn't like them and not sure if Access does either), and failed to setup an ODBC connection, I'm trying to open a recordset within my local system:

Set Conn = CreateObject("ADODB.Connection")
Conn.Provider = "Microsoft.Jet.OLEDB.4.0"
Conn.Open "http://www.mysite.co.uk/fpdb/nwind.mdb"
Set RS = Conn.Execute(SQL)

But it doesn't work! Am I at least on the right track?

Sorry, but it will never work that way with an Access back end.. IMHO, you are on the wrong track.

Most FrontPage sites I do use mySQL on a Unix/Linux web server If running an a Windows Server/IIS I use MS SQL or mySQL with FrontPage. I have found it best to NOT use an Access back end on a web site unless there will only be a few (less than 20) concurrent users and they only want to display view the data from the database.

 

[pondlife]

Thanks again HiTechCoach. As a work around I'm exploring the possibility of using the Internet Transfer Control to copy on demand the remote database file by ftp from the webserver to the local PC, from where tables in the copy can then be linked into our operations system in the normal way. The Internet Transfer Control (currently v6) is available in Access2000, and the remote webserver supports ftp access, so all i need to do now is to make it work...

(I take your point about the benefits of MS SQL, thanks, and may well change to that if this drags on.)

 

[pondlife]

Finally I've got a solution, using Internet Transfer Control, to copy by ftp the remote database onto the local PC (the C drive in the example below), from where the data can be read into the local Access database for local use.

Reference: http://msdn.microsoft.com/en-us/library/aa733648(VS.60).aspx

(You will need the Microsoft Internet Transfer Control on your PC (AND your users). If it is not in your/their ActiveX controls (ie does not appear in the list at Insert/ActiveX Control) then download http://activex.microsoft.com/controls/vb6/msinet.cab, extract the msinet.ocx file, copy it to C:/Windows/System32. Then in the Access code window got to Tools/References and browse to the system32 folder; find the msinet.ocx file, and select 'Open'.)

(1) Insert the Microsoft Internet Control ActiveX object in an Access form on the local PC and name the control 'axFTP'.

(2) Add a command button, name it 'cmdGetFile'

(3) Apply the following code to the form:

Option Compare Database
Option Explicit
Dim objFTP As Inet

Private Sub Form_Load()
' Set a reference to the Internet transfer control.
Set objFTP = Me!axFTP.Object
End Sub

Private Sub cmdGetFile_Click()
Dim strSite As String
Dim strDirectory As String
Dim strFileFrom As String
Dim strFileTo As String
Dim strUserName As String
Dim strPassword As String

strSite = "ftp://ftp.mysite.com"
strDirectory = "www/root/fpdb"
strFileFrom = "remotedatabase.mdb"
strFileTo = "remotedatabaselocalcopy.mdb"
strUserName = "myUserName"
strPassword = "myPassword"

'delete the local file (the .Execute Get ... method will not overwrite an existing file)
If Dir("C:\" & strFileTo) <> "" Then 'check if the file exists
Kill "C:\" & strFileTo 'file exists so delete it
Else
MsgBox "File does not exist"
End If

With objFTP
.Protocol = icFTP
.URL = strSite
.UserName = strUserName
.Password = strPassword
'.Execute , "DIR" ' Returns the directory.
.Execute , "CD " & strDirectory 'changes the directory
Pause
.Execute , "Get " & strFileFrom & " C:\" & strFileTo
Pause
.Execute , "CLOSE" ' Close the connection.
Pause
.Execute , "Quit"
End With
End Sub

Private Sub axFTP_StateChanged(ByVal State As Integer)
' Display a message when the transfer is finished.
If State = 12 Then MsgBox "Database has been transferred"
End Sub

Private Sub Pause()
Do While objFTP.StillExecuting
DoEvents
Loop
End Sub

In this code example I have broken the steps down to their smallest parts. The Pause function is needed so that each Execute command completes before the next is issued. If this is not done then a 35764 runtime error is the likely outcome. (The steps can be combined into a single execute command, which would avoid the need for pauses, but the individual steps are fast anyway.)

I hope this helps someone with a similar problem!

 

[HiTechCoach]

pondlife,

WOW! Thats for posting your solution. I am sure many others will benefit.