I have an Access BE that I'm pretty sure is being over worked (40-50 concurrent users) so I'm assuming the best option is to transition to SQL Express 2005.

First, what do I need to do on the server side so SQL Express will work. (i.e. How do I install it, etc....)

Second, how do I transfer the tables with their data

Third, I don't really need all the power of SQL, the functionality of the FE is find, just the performance is the issue.

Thanks.

Hi William.
You rang? :-)

If you've never done anything with SQL Server before at all - then perhaps have a look at the still (I know, I now..) embryonic SQL Server FAQ (see attached).
It doesn't cover everything yet - and it never will lol. (It isn't meant to - I ain't writing a book on it ;-)
It hints on what's there and available from Access.

You'll notice the start where it says that it's not a magic fix for all ails. That's very true.


You'll install SQL Express from your substantial download - it runs as a service (the DB engine that is).
Install Management Studio too. (Make sure you've gotten a download that offers MS with SQL Express!)
And you'll get the configuration installed tools too.
(Use the tools to make sure the engine and Browser services are both running after install).

You could use an upsizing wizard from Access to get your data into a SQL Server db...
But I prefer the SSMA ( download ).

Then you're into creating your linked tables.
(I'm assuming you're continuing with linked tables - there's much as alternative - but with substantial redevelopment required).

One advantage of the upsizing wizard is it will create your new links for you I believe...
But you need to be familiar with creating new links anyway - for deployment etc...

Otherwise - you're into link tables > ODBC and selecting your SQL DB.

While using linked tables - you'll find much as it was, the caveats at the end of the FAQ are for those getting into T-SQL (Walter wanted more detail in it ;-).
Getting the data imported and your links established is a good first step.

More later no doubt.

Dashing a bit today...



Edit: I didn't forget to attach the draft FAQ... of course not.. no no nooo

Edited by: LPurvis on Thu Nov 8 9:32:30 EST 2007.

First Q: I just installed it and followed the prompts ... Click here for a similar question from CyberCow

Second Q and Third Q: You can use the wizard, however, I like to do it "manually" with the MDB and an ADP side by side... then I copy/paste from the MDB to the ADP (SQL Server) then I make some adjusts to the tables ... Click Here for a recent discussion I just have with respect to moving the BE to SQL Server ...

... Gotta Go! ... I will check back later!

(I won! By 5 whole minutes too :-p)

... Hah ... if you saw my guesture ... you might not be smiling!! ... (IMG:http://www.utteraccess.com/forum/style_emoticons/default/evilgrin.gif) ... (IMG:http://www.utteraccess.com/forum/style_emoticons/default/laugh.gif) (IMG:http://www.utteraccess.com/forum/style_emoticons/default/laugh.gif) (IMG:http://www.utteraccess.com/forum/style_emoticons/default/thumbup.gif)

One thing I am not sure whether it is mentioned anywhere in the myriad of links is that you must include a TimeStamp Field in each of the SQL BE Tables to avoid the phantom update/write conflicts that happen fairly regularly with Access FE using ODBC-linked SQL Server Tables. With the TimeStamp Field, Access / JET works with SQL Server to identify update/write conflicts correctly.

I found that sometimes, existing complex Queries (using linked Tables, not Pass-Through) will not work correctly once the BE has been upsized to SQL Server. In my experience, Access/JET seems to send some parts of the complex Query that Access expects SQL Server to understand and to process the request but SQL Server does not understand what's being passed to it. Quite often, I need to change the Query/SQL some what (sometimes even silly changes such as adding 1 extra Field in the SELECT clause even though I don't need this Field) so that Access/JET sends different parts of the SQL String that SQL Server understands.

There is no problem with the usual run-of-the-mill Queries we often have in our databases.

Brent, Leigh:

Thanks to both of you. I am looking over the FAQ and it seems to make sense. Once I've read it a few more times, I'll probably be back with some questions.

One quick question: I need to transition all my code over to ADO rather the DAO? -Scratch-A97 doesn't support ADO.

Edited by: dashiellx2000 on Thu Nov 8 10:24:06 EST 2007.

I didn't use ADO when I used A97 but I think ADO works fine in A97 ...

Perhaps I'm misreading the References.

Is Microsoft ActiveX Data Objects = ADO?

Hi William

Yes, Microsoft ActiveX Data Objects 2.x Library is the ADO one

Thank you, Allan.

So do I need to change all my code from DAO to ADO?

Hello Van ---

>> must include a TimeStamp Field in each of the SQL BE Tables to avoid the phantom update/write conflicts that happen fairly regularly with Access FE using ODBC-linked SQL Server Tables. <<

I have used A2003 FE and SQL Server 2000 for quite some time now and have not experiences an issue with what you speak of (at least that I am aware of). We have up to 100 concurrent users on the SQL Server db, more regularly about 50-60.. NON of my tables have a TimeStamp field, but ALL have a PK... I am wondering if the requirement to prevent the "phatoms" you speak of is having a unique record identifier (a PK OR a timestamp field) ... Do you have any links to white papers or something? ... Maybe the issue was corrected with a SR or an updated ODBC driver?

Sidenote ... a "timestamp" field does NOT mean a column named "TimeStamp" that defaults to the current date and time ... "timestamp" is a datatype in SQL Server. By the same token, when I said "unique record identifier" I did not me the "uniqueidentifier" datatype... I just meant a plain ole Primary Key index.

From SQL Server 2000 Datatypes:

timestamp: A database-wide unique number that gets updated every time a row gets updated.
uniqueidentifier: A globally unique identifier (GUID).

Van -- any information would be much appreciated!

>> So do I need to change all my code from DAO to ADO? <<

NO!! ... Not by a long shot! ... If you move your properly designed data tables to SQL Server, link them up, and remove the "dbo_" prefix on your linked tabledefs, your app *should* work just as it does now.... you may have to do some slight modifications, but quite possibly none at all. I have at least an application (actually two) that is used by two separate departments ... one department uses SQL Server as the BE and the other uses JET on a network share. There is only ONE code base for the FE (note: yes each user has their own copy --- I mean the code base is the same for each department ... so I only have to maintain one code base to address the needs of both departments). It uses the linked tables (exclusively) to retreive and manipulate data. Also, the db uses DAO exclusively, which does quite well with linked tables. The only difference in the installed apps for each dept. is the BE that the linked tables point to, which is a configuration setting in the app.

You CAN, however, take advantage or a server based db buy using ADO ... but ... it is NOT required!

Brent,

I've had a similar experience as yourself... I've almost never had a need to use the TimeStamp field. I think that's probably because I use ADP's almost exclusively when working with SS. The only time I had an issue is when a MDB was involved and a unique identifier could not be determined by Access. (I believe this may also be the case with MySQL)

I also agree with your comments about DAO/ADO in that it doesn't matter which you use. But my personal take on it is a little different.... Somewhere down the road I picked up the notion that ADO can connect to more sources easier, (don't actually know if this is the case) so I always use ADO. I know that in some instances DAO can be faster, but I have found those instances few and far between. To me the speed difference is negligible enough that I find the benefit of using a single library (ADO) over two (and trying to keep both syntaxes straight in my mind) outweighs the cost.

...So even when I build standalone MDBs, I still use ADO.

Hey Walter --- I haven't seen you in a while! ... good to see you!

...

ADO can definately connect to more data sources! ... With William using A97 I wanted to encourage him in that he DOES NOT HAVE to convert to ADO ... Also, with respect to my dual back end application, the KEY is that all data manipulation and retreival is THROUGH the linked tables (ie: Internal Access Objects). Whether the data is manipulated with ADO or DAO in the code is really just a matter of preference...as long as you use the linked tables for your source of the data. By using ONLY the linked tables, the code does not have to change when the Back End format changes. When the format changes, all that is needed is a re-link (and re-name the ODBC linked tabledefs to remove the owner prefix) and the app is good to go again.... with NO code changes.

If my code connected DIRECTLY to the Data Source via ADO (or DAO for that matter), I would have to modify my connection strings and/or my SQL statements used in code to reflect the back end that has been configured. You can, of course write code to return the correct connection string, as well as the correct SQL statement, but I find it easier, and more flexible, to just maintain the linked table defs ...

As a point of note, the app in question can actually use ANY db that has ODBC drivers (MySQL, Oracle, SQL Server, etc ... ) without any code changes to it ... simply because all data is manipulated and retrieved through the linked tables. Now with this flexibility, there is often a cost of performance (or the inability to capitalize on performance improvement opportunities), because of the exclusive use of linked tables (that means the queries use the linked tables too --- so, no passthru's!) ...

... Ok ... I think I've gotten wordy enough! ... so ... thats it for now.

I see this has become quite a busy thread...
I'll try not to pollute it now then - but I'll touch briefly on a couple of points.

Van's mentioning of maintaining a TimeStamp field strikes a chord in that I KNOW I've read of an issue it fixes (/circumvents) though the exact issue eludes me at this very moment.
At a general level - possibly related to the old #Deleted# errors in linked tables (yes even though a PK *should* be enough...) and also very likely concurrency issues.
The fact that it doesn't always manifest as a problem doesn't mean it isn't a good practice, worth doing anyway. ;-)
FWIW the SQL Server Migration assistant for Access adds TimeStamp fields by default to all converted tables.
(So Microsoft must feel it's a good idea).

Equally Van's reference to running ADO in 97 working is correct.
Obviously there is no trivial local database object (CurrentProject) however that's not a problem for the external access. As I recall there are possible problems with hooking ADO events in '97 class modules - they're just "flighty" I believe (i.e. may create OK - but won't necessarily remain robust).
The example I found was create an ADO event procedure in 97 class, copy the module's code, delete and paste it back in. Access will try to reformat the event definitions and it will not compile.

However that's me getting a bit off base while we're supposed to be offering advice to William.


I agree with the premise of sticking with linked tables and DAO (*certainly* for now - and quite likely for as long as while using 97 - perhaps longer).
You'll want to bear in mind that sometimes data operations might be executed client side.
This is basically when Jet and the ODBC provider haven't been able to form a SQL neutral string from your request based on the Access query. Poor performance will result - and you'll feel it. You can start looking into creating other server based objects then.

Equally you'll want to be stingy with your data requests when operating in a Client Server scenario. (OK maybe careful will do - but aim for stingy ;-). Indeed IMHO - maintaining that mentality *all* the time is a worthy endeavour.
Base your forms on as few records as possible - your lists and recordsets too.
Update only what you need to - only when you need to.
Remember that a Requery is another hit on the database too.

Almost none of this is vital - but all of it is important (to give you a good, scalable application).

When you want to start getting in to T-SQL, then ADO and passthroughs await with much fun.

Hi Brent

I am at work at the moment so I have to be brief (not Leigh's and your definition of "brief" (IMG:http://www.utteraccess.com/forum/style_emoticons/default/sad.gif) ... )

I have fixed the "Write conflicts" problem by adding the TimeStamp Field in 3 databases (none is mine as I always have TimeStamp Fields), 2 on SQL Server 2000 and 1 on SQL Server 2005.

[*** Aside: One database was a big nice business bonus! As I was too busy to spend enough time with one of the clients, he went out and got another developer to upsize his MDB Back-End to SQL Server Back-End (SQL Server 2005 that comes with Windows 2003 SBS R2). After the upsize, users cannot edit records in the main Table (Orders) and the other developer tried all sort of things (including re-upsizing Back-End, re-installing SQL Server) for 2 weeks without getting anywhere. The clients embarrassedly asked me to come back and I fixed the whole thing in less than 30 mins by adding the TimeStamp Field to this Table and re-linked about 10 copies of the Front-End. Since then, the client is willing to wait for me to do his database work, always.***]

Even if the "Write conflicts" problem doesn't occur, there are efficiency gains in using the TimeStamp Field with ODBC-linked Tables in Access Front-End.

I explain later and dig in my docs to see if I can find relevant links for you ...

Thanks for the info Van ... I suppose it really doesn't hurt anything by sticking a timestamp field on the tables, I could do it programatically so it would be virtually painless, sounds like its a good practice, as you say MS thinks it a good idea .... but their are A LOT of things MS has thought of that were not such good ideas (ie: The Ribbon Interface (IMG:http://www.utteraccess.com/forum/style_emoticons/default/blush.gif) ) ... but ... this one seems like its a good idea.

.... Hows that for brief! ... (IMG:http://www.utteraccess.com/forum/style_emoticons/default/thumbup.gif)

Walter, Brent, Leigh and Van:

Thanks so much for continuing this thread. Reading you comments has help me a great deal and I encourage you all to continue. Van, I would really like to see any docs you have that may explain the record-locking issue.

Temporarily, I was able to improve performance using some temp tables, but of course this is only a stop-gap fix (or a MacGyver if you prefer). I am still looking into upgrading to an SQL back-end, but unfortunately, I have to bring my IS department into this and they will take forever......

I will let you know what I learn and post back any issues, etc....

Thanks again.

I love it when that happens. (IMG:http://www.utteraccess.com/forum/style_emoticons/default/cool.gif)

I agree, you probably should use timestamp fields, especially with tables linked to Access FE.