X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> First Step With SQL, Any Versions    
post May 23 2020, 07:51 AM

Posts: 157
Joined: 22-January 08

I have been using access tables for years. This week our IT department converted my files to SQL. I have no idea what I am doing, but excited to learn. I am in development and only have access to my files - thank goodness

my first question is - - we opened a new database and link to the server. Am I correct to understand that this file is the back end? Then the user front end files will be linked to this?

my second question is - - - will I have to rebuild everything? my queries, forms, modules, macros, reports?

Thanks for all of your help
Go to the top of the page
Jeff B.
post May 23 2020, 07:55 AM

UtterAccess VIP
Posts: 10,491
Joined: 30-April 10
From: Pacific NorthWet

It may be a matter of terminology …

In Access, you have tables holding data. In SQL-Server, you have tables holding data.

When you create an Access database and 'link' to the SQL-Server tables, your Access db is the "front-end", and the SQL-Server db is the "back-end". Your users would each get/use a copy of that "front-end" (but you'd probably not want to have any/many tables in the front-end … that kinda defeats the purpose of having the linked back-end).


Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
post May 23 2020, 08:28 AM

UA Admin
Posts: 37,510
Joined: 20-June 02
From: Newcastle, WA

It is a bit more nuanced than "files", but essentially, you have a Front End in Access. This front end contains the interface objects--forms, reports--and data manipulation code--VBA or macros. Queries are part of the interface layer, and usually are also in the Access Front End. The "Back End" is actually a server-based Relational Database Management System, or RDBMS, not a single file. It contains, among other things, the tables in which data is stored.

However, it's not really all that cut and dried because SQL Server includes other very powerful tools. For example, you can create "select queries" in SQL Server, which are called views. To perform the same functions as Update, append, and delete queries, SQL Server provides Stored Procedures. Both Stored Procedures and Views can be significantly more sophisticated, than Access queries, although they are quite similar functionally for the most part.

You may need to modify your Access interface to work better with SQL Server. It is often reported that performance is negatively impacted when local Access tables have been moved to a remote SQL Server. That, of course, depends a lot on your designs now.

Another main advantage of SQL Server is data security. SQL Server security is better. You'll want to invest some time and effort learning to take advantage of that in your relational database application.

My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
post May 23 2020, 03:56 PM

Posts: 157
Joined: 22-January 08

Thank you for your replies. Good info as I start on my trip
Go to the top of the page
post May 24 2020, 06:13 AM

Posts: 1,123
Joined: 26-January 14
From: London, UK

If you haven't done already then I suggest you install SQL Server Management Studio. It's the best way to practice and develop SQL with SQL Server.
Go to the top of the page
post May 24 2020, 02:17 PM

UtterAccess VIP
Posts: 3,103
Joined: 12-April 07
From: Edmonton, Alberta Canada

Hopefully someone has a "link" to some of the steps.

In general this should cover 99% of what you need to know:

All tables need a PK. Access databases can often work well without a actual PK in the tables, SQL server does not.
(thankfully few tables even in typical access applications break this rule).

All tables that you going to edit? In addition to a PK, they also should have what is called a rowversion column (they are called timestamp column, and they have ZERO to do with datetime, and are not to be confused with time, or anything to do with datetime). So, you want PK column, and also this "rowversion" column. (access uses this column to determine when records been changed. If you don't add this column, then access will revert to a column by column compare of edited data - it causes quite a few problems).

The upsizing wizards (built in one - available up to access 2010, or SSMA will add this column for you). However, it not clean if you use SQL studio (SSMA) to import the data, or you used Access or better yet SSMA (SQL tools migration assistant for Access). You don't have to use SSMA, but if the tables were JUST imported using SQL manager?
Then all of your relationships weill be lost, but WORSE all your indexing of columns for high performance is lost.

So, if you have quite a few tables, and they were related? You might want to re-migrate using a better approach, since re-adding all those indexes and setting up the relationships can be quite a bit of work. So some tools do a FAR better job of migration of data, and keeps things like relationships, PK, and indexing intact.

Next up:
VBA recordset code:

A typical snip of VBA to open a recordset will look like this:

dim rst       as DAO.RecordSet

Set rst = currentdb.OpenRecordSet("some table")
Set rst = currentdb.OpenRecordSet("select * from tblCustomers")

If the above tables are to allow editing, and in fact in general, you have to change above to:

Set rst = CurrentDb.OpenRecordSet("select * from tblCustomers", dbOpenDynaset, dbSeeChanges)

Now, even in a fairly large application, you can in the code editor do a GLOBAL search for OpenRecordSet. I find that you can find + make the above change even in a rather large application in about 15-25 minutes of time. (just put , dbOpenDynaset, dbSeeChanges in your paste buffer).

Once you done the above?

And assume you have the SAME name for your tables?

At this point, 99% of your application should now work.

The other got ya?
In access (VBA recordset code), or even in a form?
When that data becomes dirty? Well, the PK autonumber is INSTANT available for use with a Access back end. In SQL server land, you cannot grab/use the PK id until the reocrd is saved.

So, once again?
In a large applcation, there is ueally only about 2-5 places where code (or in forms) were you need to pick up/grab/use the newly added PK value.

In a form?
If the user starts typing, then with access back end, the autonumber "instant" appears, and form event code etc. can use/get/grab the PK id.

In SQL server, you have to force save the new reocrd BEFORE you code grabs the PK id.

So, if you have this:

   dim lngPK    as long
   lngPK = me!id        ' we are grabbing the new PK value
   bla bla bals.

with SQL server, you have to go:
   if  me.dirty = true then me.dirty = false.
   lngPKD = me!id

Now you ONLY need to do the above WHEN code grabs the new PK id, and the record NOT been saved as of yet. As noted, even in a realative large applcation, I find only a few places where this occurs.

At this point? The above rules and changes should result in your application working 100% the SAME as before. This includes VBA code, queries you built, reports and darn near everything.

The next step?
Well, you can start to address performance problems and issues that arise. Depending on how well the application was built, then the amount of work and changes to increase performance will much depend on how well searching, and and filtering etc. was done before. The better the existing designs were in regards to limit the pulling of data into a form, then the better the application will behave with SQL server.

So, in a nutshell, the above will get 99% of your application. If ANY THING like date filters etc. stop working? Then do NOT go on a code chopping and witch hunt. The date filters and EVERYTHING you have should work. One last big tip:

Check the data types of the linked tables, this is ESPEICALLY the case for date/time columns. Be 100%, if not 500% absolute but absolute sure that Access sees the datetime columns as dates, and NOT as text. I seen so many posts here on UA that after a migration that the poster is wondering why their date filtering code is broken. Then everyone piles in with great intentions, offers all kinds of code changes. The problem is that OFTEN during a migration that date columns are converted into datetime2 types of columns in SQL server. For now, you REALLY (but really really!) want to check + ensure that the columns went up and are of datetime (both datetime, and datetime2 columns can work for access, but the default access ODBC drivers ONLY work with datetime, and the migration wizards and assistants tend to default as datetime2 type of columns. I STRONG beyond anything suggest you double, triple, quadplex check this issue.

(in other words, make sure that date columns from Access are now datetime columns in SQL server, and NOT datetime2).

So above really is all you need for now to get up + running. If you follow above rules, then your application, VBA code, forms, reports, and existing queries you have should all work, and work without any changes.


Go to the top of the page
post May 27 2020, 06:42 AM

Posts: 157
Joined: 22-January 08

AlbertKallal - thank you for your reply Alot of information there to take in, and I do appreciate it I do have a primary key in all of my tables. It does appear that after migration to the SQL server there is the timestamp field that you mentioned I will review and check into the other info you provided

nvogel - I will check on your server management tool tip - thank you
Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    13th July 2020 - 05:48 AM