473,327 Members | 1,952 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

Guidelines for Access FE changes required for SQL backend? MySQLbackend?

Anyone know of any list of changes required in an Access FE to make the
Access FE work with a SQL backend?

ditto for Access FE working with a MYSQL backend?

Bob
Nov 4 '06 #1
2 1448
Bob Alston wrote:
Anyone know of any list of changes required in an Access FE to make
the Access FE work with a SQL backend?

ditto for Access FE working with a MYSQL backend?
It depends on how the question is framed.

If you replace every table in your Access app with a link to a table on a SQL
Server having the same field definitions then the app will work even if you do
nothing else at all. Access and whatever VBA code you might be using doesn't
really care where that linked table resides. Only that it be named and have the
appropriate field names for what Access is trying to do with it.

Now...will it work well or even as well as before? That is a different
question. Most would be surprised to learn that it might work pretty darned
well just like that. Access/Jet does a good job of getting the server to
perform much of the work in that scenario. This might not be "client server
optimized", but rather than having to do a major overhaul what you usually find
is that there will be some areas that perform slowly or which perform "okay" but
where you can tell that an unnecessary amount of data is being pulled over the
LAN and those are the areas to concentrate on for rework.

Rework will normally involve replacing some of the built in mechanisms like
"find" with methods more suited to a server back end, building some server side
Views and Stored Procedures where complex joins and/or processing is being done,
and generally making sure that the app only pulls the minimum data for any given
task. Forms that just open against unfiltered tables with thousands of records
in them for example is a bad idea in a client server app.

Notions that the entire app must be re-written using ADO instead of DAO and that
ALL interaction with the server must be done with Views and Stored Procedures
are just flat wrong.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com


Nov 4 '06 #2
Bri

Bob Alston wrote:
Anyone know of any list of changes required in an Access FE to make the
Access FE work with a SQL backend?

ditto for Access FE working with a MYSQL backend?

Bob
I agree with almost everything that Rick said in his response with a few
exceptions (for both MS SQL and MySQL):
- Include a Timestamp Field in all tables. In MS SQL this is a field
that you can't use for anything else, but in MySQL it is a user readable
DateTime field so it needs a special default value to work in this
capacity. The timestamp field allows Access to determine if another user
has edited the record while you have been editing it. You won't be able
to do bound edits without it. Adding it to your tables does not impact
what you have coded in your FE.
- Editing Recordsets require that you open them with the dbSeeChanges
option.
- When adding new records via .AddNew in a recordset, the AutoNumber
field is not generated until AFTER the Update rather than BEFORE it (Jet
does it at the .AddNew). So, if you add records via recordsets where you
need to know the PK of that record so you can add Child records, you
will need to adjust your code. Since it doesn't create the Autonumber
until after the Update you no longer have the record current. There are
a few ways to deal with this, the best being to try and not add records
this way and come up with an SQL Insert replacement.
- Queries with several tables and joins make Access think that it can't
send efficient request to the BE make it bring over huge amounts of data
to do it locally. These will make themselves evident fairly quickly.
These can be converted to PassThrough queries if you want/need to keep
the business logic in the FE or into Views or SPs if you don't.

There are a few other things to watch for, but are fairly rare. There is
a page out there somewhere with a lot more info in it on this topic....
OK, found it:
http://www.granite.ab.ca/access/sqlserverupsizing.htm

Hope that helps

--
Bri

Nov 5 '06 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

63
by: Jerome | last post by:
Hi, I'm a bit confused ... when would I rather write an database application using MS Access and Visual Basic and when (and why) would I rather write it using Visual Studio .Net? Is it as easy...
28
by: Lee Rouse | last post by:
Hello all, This is going to be a rather lengthy "question". I have an Access 2k database, separated front end/back end. Front end copies are on about 30 workstations and used frequently during...
13
by: Manuel Lopez | last post by:
I have a puzzling form timer problem that I didn't experience prior to Access 2003 (though I'm not sure access 2003 is to blame). Here's the situation: a computer has two access 2003 databases on...
6
by: Terry Bell | last post by:
We've had a very large A97 app running fine for the last seven years. I've just converted to SQL Server backend, which is being tested, but meanwhile the JET based version, running under terminal...
4
by: Mark | last post by:
Hi All, I created a database for a friend a few months ago which has been working fine. However, he now wants a few changes implemented to accommodate his growing needs. When I gave him the...
49
by: Mell via AccessMonster.com | last post by:
I created databases on Access 2003 and I want to deploy them to users. My code was also done using 2003. If they have Ms Access 2000 or higher, will they be able to use these dbs with all code,...
17
by: shineofleo | last post by:
Here is the situation: I wrote a VB programm, which stores all the information in a single Access database file using jet engine. It worked well, however one of my customs reported that there was...
64
by: John | last post by:
Hello there, Im cursing my place of employment...and its taken me a month to realise it... The scenario: Ive just stepped into a role to migrate an access database to VB.Net. The access...
2
hyperpau
by: hyperpau | last post by:
Before anything else, I am not a very technical expert when it comes to VBA coding. I learned most of what I know by the excellent Access/VBA forum from bytes.com (formerly thescripts.com). Ergo, I...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.