Moving to SQL Server (1 Viewer)

Mackbear said:

If this is the case then, do i simply import the tables to SQL and create linked tables to the FE?

Click to expand...

Correct. Choose Export from the External section of then ribbon and choose ODBC and then follow along. You will need to determine what kind of DSN (File or Machine) you want to use - whole other topic there, I eventually went to DSN-less.

There is an excellent tutorial by Steve Bishop about migrating to SQL Server. Highly recommend. Here is a link the the first video and then you can go from there.

https://youtu.be/e9MMDDI1pUI

Best of luck!

This is only a side comment, but here goes:

In SQL Server you have rowversion. In ORACLE (which is what we used for certain U.S. Navy Reserve databases) the same concept is called "instantiation number" and I believe it serves the same purpose as described in that article.

An idle DB doesn't change Instantiation Number at all. Among other things, if you were trying to recover a database from backups, your goal was to restore all tables to the same Instantiation Number because that means you had a "snapshot" of the data that was valid for the time interval during which the I.N. was that value.

Sounds easy, right? Wrong. I'll keep out of the weeds and simply tell you that if you are doing remote data mirroring and the host site goes down bad enough for the remote site to have to take over, it is often a nightmare to restore from backup to achieve a uniform Instantiation Number. I actually won a bet with the vendor of the replication system by predicting that their configuration would never achieve a valid restore-set. Got a freebie steak dinner from Cattleman's Restaurant in Fort Worth over that bet.

But the point of rowversion is often not merely to somehow support transactions but also to give you a reference "I.D." that would be your target for data recovery.

i think this is how i already built my FE, everything is filtered according to what the user needs to work on

Click to expand...

Not sure if you have understood what Pat was saying or whether it is your termininology. Filtering occurs in forms after you have brought across the recordset. Pat is saying apply criteria before you bring across the recordset.

Note if you use the WHERE parameter for docmd.openform, this is actually a filter.

So instead of using the openform where parameter or in code something like

me.filter="CustID=" & 123

you build it into the recordsource

me.recordsource ="SELECT * FROM myTable WHERE " & "CustID=" & 123

Thanks CJ. Sometimes I need to be clarified

MackBear, If you are lucky enough to be using A2010 or earlier, you can use the upsizing wizard. Otherwise, you need to use SSMA or SQL Server itself. SSMA has been a nightmare for me since trying to use it completely hosed my entire system because of bad instructions on how to get around the database engine issue that MS caused when they made SSMA 64 bit only. So be verrrrrrry careful. I haven't had to upsize anything in a couple of years given that I am mostly retired and in maintenance mode with my clients and not taking on new ones. But, one of the options when you use SSMA is to add rowversion to all tables. I recommend that you do. it makes updating more efficient given how Access interacts with the server.

One thing I have learned from transitioning to sql be, is that you realise access is very quick and easy to work with. It will make you consider going back to access....

If you want to change or modify your back end when its in sql, prepare for pain.

Things that access just does automatically, dont happen automatically. For example, primary IDs, being able to just do anything to any table, this is something that has to be "allowed". Datatypes are not immediately all recognisable or sensible (in my opinion).

As for your front end, most will be fine, but you may notice some vba needs to be modified. In my case its mainly due to the fact that with access as soon as some data is created for a record, the record is created, in sql, its not. A few dlookups had to be modified, some procedures needed to be rethought through.