ADP (Write to local if loses connection to remote SQL Server?)

[kennethgrice]

Hi,

I was thinking about using an ADP for a test form application.
Originially I was going to use Access, but I have too many fields.
It is about a 10 Form Test process. I was worried that in the middle
of the application, if the remote SQL Server becomes disconnected for
some reason, the tester would be stuck and couldn't go on. (My forms
are set to insert data from the form to a remote SQL Server by
clicking a Save button. Another command button then appears to
continue the test by launching the next for.

Is there some kind of code to put in the save button to send the data
to the SQL Server on the local machine if the default connection
cannot be reached? It would also be nice to notify an admin by email
if this happens to let them know that some of the data may be in the
remote table and the rest went to local. Or just making a label
appear on the form with this info if this is the case. Also, can the
data be saved in a local SQL Server in parallel to being sent to the
remote SQL Server. That would be a good backup.

Is any of this possible? If so, does anyone have an example? Thanks
in advance. I don't see the remote SQL Server going down much, but I
am just looking at failure modes right now.

 

[Sylvain Lafontaine]

When you say that you have too many fields, I suppose that you exceeding the
maximum of 255 fields from Access MDB ODBC linked tables?

Unlike MDB databases, there are no local table with ADP. You can install a
local instance of SQL-Server (probably SQL-Server 2005 Express) but catching
the disconnection failure from ADP and reconnecting to the local instance
will be very tricky because ADP doesn't really help you in this matter.

A better idea would be to always connect to the local instance of SQL-Server
and use Replication between it and the remote server. However, setting
replication over the Internet (WAN) will also be tricky.

If you are really concerned with connection failures, you should look at
something else then ADP to update your databases. I would say that your
possibilities are:

1- MDB database and exclusive use of unbound forms; so that you can
control 100% of the updating process.

2- Replication

3- .NET technologies.

4- Forget about this problem.

 

[Tom Wickerath MDB]

hey kenny

you just need to learn how to use SQL Server, kid

 

['69 Camaro]

Everyone please note that Aaron Kem.pf is attempting to impersonate one of
our regular posters again. Tom would never post such a message.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[Aaron Kempf2]

gunny

you having fun yet, kid?

Everyone please note that Aaron Kem.pf is attempting to impersonate one
of our regular posters again. Tom would never post such a message.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[Paul Shapiro]

You might reconsider your data structures. I've never seen a case where the
mdb field-per-table limit was a problem. You didn't describe the data but
I'll guess. If you have the questions as individual fields, for example, you
could add a Question table that had columns for the form number, question
number, question text and question answer. Then you'd have many rows for
each form instead of many fields. Etc.

 

[aaron.kempf]

you've never seen a case where you needed more than 255 columns?

I believe that a whole ton of ERP applications has more than that..

gosh; I believe that even MS Project has more fields than that