Use of Linked tables via ODBC to SQL server over wi-fi

[Chris W]

Hi All

We all know that attempting to run a split FE/BE (with the backend as a MDB)
across anything other than a LAN is not a good idea, but if the backend is a
SQL Server (or similar), are there the same issues if the FE is via linked
tables using ODBC and the connection is unstable (e.g. Wi-fi, internet) -
assuming of course that the system is coded so that all updates are passed
back to the server?

Or would it be better to not have any links at all, and do the whole lot via
ADO and XML - although from what I can see, there would be a lot more
development effort in this solution

Any comments or thoughts would be welcome

 

[Stefan Hoffmann]

hi Chris,

We all know that attempting to run a split FE/BE (with the backend as a MDB)
across anything other than a LAN is not a good idea, but if the backend is a
SQL Server (or similar), are there the same issues if the FE is via linked
tables using ODBC and the connection is unstable (e.g. Wi-fi, internet) -
assuming of course that the system is coded so that all updates are passed
back to the server?

In short: Yes, of course. For your linked tables Access uses one session
as long as it is open.

Or would it be better to not have any links at all, and do the whole lot via
ADO and XML - although from what I can see, there would be a lot more
development effort in this solution

If you really need to drive it over WiFi, you have only two possibilities:

- use ADO and short living recordsets and connections
- use a local SQL Express or MSDE with replication as local BE


mfG
--> stefan <--

 

[Chris W]

Hi Stefan

Thanks for the quick reply.

In short, I have to go down the wi-fi route as that is the configuration my
client is usig, so I am looking for a solution that will work across this
network.

If I go via the replication route - which is a scenario I had not really
considered - then is there any benefit using SQL replication over Jet
replication?

How "hands-off" is the replication route - I have never used it before, but
how does the system handle a clash and inform the correct user that their
changes have been discarded - does it do that?

 

[Stefan Hoffmann]

hi Chris,

If I go via the replication route - which is a scenario I had not really
considered - then is there any benefit using SQL replication over Jet
replication?

I'm not sure how stable or reliable Jet replication is, but I have used
the MSDE/SQL Express scenario a few times. I just prefer using MSDE/SQL
Express, cause it is easy to setup and completly transparent for the
Access FE.

How "hands-off" is the replication route - I have never used it before, but
how does the system handle a clash and inform the correct user that their
changes have been discarded - does it do that?

As i said before, it is easy to setup, but you need some knowledge and
experience: so just try to setup a simple test system.

http://technet.microsoft.com/en-us/library/ms151819.aspx

mfG
--> stefan <--