473,327 Members | 1,967 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.

Upsized MDB to SQL

We have upsized tables to SQL via the upsize wizard tool and are now using an mdb file as the front end with these linked tables.

We are experiencing significant performance issues when opening forms. To note, most forms are fairly complex with multiple subforms running mutliple queries within.

We have put more of the "robust" queries on SQL as views and linked to these via tables in our mdb which slightly improved speed, but still a major issue.

Has anyone experienced this? Are there any suggestions for improvement?

Thanks in advance
Sep 6 '07 #1
2 1494
Jim Doherty
897 Expert 512MB
We have upsized tables to SQL via the upsize wizard tool and are now using an mdb file as the front end with these linked tables.

We are experiencing significant performance issues when opening forms. To note, most forms are fairly complex with multiple subforms running mutliple queries within.

We have put more of the "robust" queries on SQL as views and linked to these via tables in our mdb which slightly improved speed, but still a major issue.

Has anyone experienced this? Are there any suggestions for improvement?

Thanks in advance

Having managed to get your tables into SQL server is a good start but unfortunately only a small part of the whole upsizing experience IMHO.

I am assuming your tables are successfuly installed on the server with correct datatypes and sizes mapped out for the fields and that you have all the relevant primary keys identified, set and indexes created, where relevant. The upsizing wizard is a good labour saving tool but frankly quite often makes incorrect assumptions.

I have to say it very much depends on the complexity of your MDB file. Suffice it to say, in my experience I have invested in rewriting frontend files more often than enough, rather than make do with the original. Why? well generally I've found that MDBs using JET have so much interwoven code in the background (and why not of course it is meant to work with JET on the client PC - that is what it was designed for) that it becomes compromised in terms of scaleability so much so, that it takes more time to convert the code than to rewrite it.

The utopia for working with SQL server is to use 'Stored procedures' these are precompiled processes working to an execution plan. Because they are precompiled the performance gain is huge.

Does your database have embedded SQL behind the forms that encapsulate inefficient or unduly complex functionality maybe joining on columns that have not been indexed on the server? firing SQL to the server hundreds of time maybe in a code loop rather than in a batched process on the server or maybe even returning inefficient datasets like SELECT * FROM TheBiggestTableInTheUniverse..........the list can go on and sound like a time consuming horror story in having to deal with a troublesome slow MDB file linked to SQL Server.

The reality is.....performance should be 'substantially' improved not to mention concurrency which invites me to wonder if there are design issues with the MDB file itself in what it is doing functionally in the 'client server' environment.

Depending on your version of Access there are two types of frontend file for working with SQL Server one the .MDB file and the other the .ADP file (I personally 'much' prefer the ADP for working with server side records thats a personal choice, but it is one that I recommend you at least look at because it functionally is the same as the MDB in terms of report capability, screen layout appearance etc you get the added advantage of seeing views and stored procedures in the frontend too)

It may be that your MDB is, as you outline, 'complex' and relied so much so that you feel you have to 'remain with it', in which case I'm afraid you have no option but to fine tooth comb it to track the performance issues. Multiple subforms on a single form is for me a big no no Generally speaking one subform is quite sufficient and you merely swap its sourceobject if needs be. The idea being to minimise calls to the server for data otherwise it is going to be slow dependant on how many datasets are being opened at any one time this includes dropdown comboboxes not in use, (the datasource being set runtime when the dropdown gets the focus on screen. All of these techiques are matters of common knowledge with experience.

I'd be interested to know how a freshly created MDB file (ie: a new one with no forms reports macros modules and code) linked to the server performs in returning your datasets.IF it is slow you have ODBC,network or server database issues IF it is fast... then logically it cannot be the MDB format itself and therefore the evidence against the erstwhile the main frontend file you have becomes greater.

I'd be interested to know how a freshly created ADP file (ie: a new one with no forms reports macros modules and code) linked to the server performs in returning your datasets. If this is slow then you 'definitely' have a network issues or a server side database configuration problem.

Either way all of these things to eradicate the problem as I am sure you aware anyway, is a process of elimination unless its blindingly obvious. I'd be interested to know how you get on with this because the long and short is you SHOULD be getting greatly enhanced performance... and no I have not had problems myself (in answer to your penultimate question)

Regards

Jim
Sep 7 '07 #2
Jim Doherty
897 Expert 512MB
Having managed to get your tables into SQL server is a good start but unfortunately only a small part of the whole upsizing experience IMHO.

I am assuming your tables are successfuly installed on the server with correct datatypes and sizes mapped out for the fields and that you have all the relevant primary keys identified, set and indexes created, where relevant. The upsizing wizard is a good labour saving tool but frankly quite often makes incorrect assumptions.

I have to say it very much depends on the complexity of your MDB file. Suffice it to say, in my experience I have invested in rewriting frontend files more often than enough, rather than make do with the original. Why? well generally I've found that MDBs using JET have so much interwoven code in the background (and why not of course it is meant to work with JET on the client PC - that is what it was designed for) that it becomes compromised in terms of scaleability so much so, that it takes more time to convert the code than to rewrite it.

The utopia for working with SQL server is to use 'Stored procedures' these are precompiled processes working to an execution plan. Because they are precompiled the performance gain is huge.

Does your database have embedded SQL behind the forms that encapsulate inefficient or unduly complex functionality maybe joining on columns that have not been indexed on the server? firing SQL to the server hundreds of time maybe in a code loop rather than in a batched process on the server or maybe even returning inefficient datasets like SELECT * FROM TheBiggestTableInTheUniverse..........the list can go on and sound like a time consuming horror story in having to deal with a troublesome slow MDB file linked to SQL Server.

The reality is.....performance should be 'substantially' improved not to mention concurrency which invites me to wonder if there are design issues with the MDB file itself in what it is doing functionally in the 'client server' environment.

Depending on your version of Access there are two types of frontend file for working with SQL Server one the .MDB file and the other the .ADP file (I personally 'much' prefer the ADP for working with server side records thats a personal choice, but it is one that I recommend you at least look at because it functionally is the same as the MDB in terms of report capability, screen layout appearance etc you get the added advantage of seeing views and stored procedures in the frontend too)

It may be that your MDB is, as you outline, 'complex' and relied so much so that you feel you have to 'remain with it', in which case I'm afraid you have no option but to fine tooth comb it to track the performance issues. Multiple subforms on a single form is for me a big no no Generally speaking one subform is quite sufficient and you merely swap its sourceobject if needs be. The idea being to minimise calls to the server for data otherwise it is going to be slow dependant on how many datasets are being opened at any one time this includes dropdown comboboxes not in use, (the datasource being set runtime when the dropdown gets the focus on screen. All of these techiques are matters of common knowledge with experience.

I'd be interested to know how a freshly created MDB file (ie: a new one with no forms reports macros modules and code) linked to the server performs in returning your datasets.IF it is slow you have ODBC,network or server database issues IF it is fast... then logically it cannot be the MDB format itself and therefore the evidence against the erstwhile the main frontend file you have becomes greater.

I'd be interested to know how a freshly created ADP file (ie: a new one with no forms reports macros modules and code) linked to the server performs in returning your datasets. If this is slow then you 'definitely' have a network issues or a server side database configuration problem.

Either way all of these things to eradicate the problem as I am sure you aware anyway, is a process of elimination unless its blindingly obvious. I'd be interested to know how you get on with this because the long and short is you SHOULD be getting greatly enhanced performance... and no I have not had problems myself (in answer to your penultimate question)

Regards

Jim

Given there is no reply As a PS I am on vacation as of today (off to sunny cyprus) so if you don't get a resolution you can PM me and I'll continue on return next week?

Jim
Sep 11 '07 #3

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: Bob Davies | last post by:
I have upsized an Access database into SQL Server and manged to get the data in place ok. The wizard created an Access Project which I have started to modify. However, I am also trying to get...
0
by: ImraneA | last post by:
Hi there I had pleasure of upsizing Access v97 db to Access v2K/SQL 2K. Wish to provide some knowledge gained back to community - hopefully help others. 1.Using Upsizing wizard - will...
4
by: Sharon Stern | last post by:
I have a form with a subform on it. When the user enters information into the form, I create a sql query in vba and use it to open a recordset. Then I set the subform's recordset to it. Using...
1
by: rcmail14872 | last post by:
I used the upsize wizard to change my Access 2002 database to a SQL Server ADP project. I had a form where the user entered a value into a text box and when a command button on the form was...
12
by: Mike | last post by:
I have an Access DB that I upsized to a SQL server DB. The tables that I upsized I can't seem to modify. I wanted to insert some data into the table and I am getting the following error: ...
1
by: Bruce Le Favre | last post by:
I upsized (using Upsize Wizard) ACCESS back end to MSSQL 2000. Front end is still ACCESS. I'm getting error when trying to add or updated a record entry from front end. The error message reads:...
10
by: jpatchak | last post by:
Hello all, I recently tried upsizing a DB with the Access Upsizing Wizard. I ran into the standard problems with datatypes and have to rebuild some queries that didn't get upsized. I am having a...
3
by: sprasad123 | last post by:
Hi there, Wondering someone can thow some light to my problem Access Problem. I have upsized a Access DB to SQL and also got a Access Linked table as a front end. Relinked the linked DB to the...
1
by: marcf | last post by:
Ello everyone, Ok i've just upsized the database to SQL Server 2005, here is the code: Set con = Application.CurrentProject.Connection Set rs = New ADODB.Recordset rs.CursorType =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
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...
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: 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
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
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
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

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.