By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,851 Members | 1,172 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,851 IT Pros & Developers. It's quick & easy.

Upgrading from access

P: 78
A database I am working on is reaching its limitations by using access.

I am considering upgrading to SQL server or mySQL.

The intent of the project is to start off by using access as the front end and the other as the back end. This is just for the introductory phase.

The plan is to eliminate the use of access all together. However I will be learning one of these systems from scratch. I understand most of the concepts of relational databases, just not the specific details of either system.

As a wider scope of the operation, the database will start out with a single server and 3 clients over the in house network. The greater plan is to involve web access for multiple users.

My major concerns for the project are:

Total cost of ownership
Learning curve

If anyone could offer any advice why to choose or not choose one system over the other. I understand the details are sparce. I'm just looking for general discussion.

Thanks for you help!
Jan 11 '12 #1
Share this Question
Share on Google+
11 Replies

Expert 100+
P: 446
This is an interesting question. I'm constantly asking it myself and I wish I had the answer! It is a big subject, and like any request for directions, the answer depends where you are starting from!

If you only have moderate Access capabilities and ultimately intend to eliminate Access altogether (as in your post) then you would be better off learning something else (to be decided)

However, it sounds like you have quite a big investment in an Access database at the moment. You say that it is reaching it's limitations. In what way? Size of data? Speed of response? Number of users? Internet access?

One cheap way to upsize to Microsoft SQL Server (MSSS) is to download the SQL Server Express package for free. You will then have to migrate your Access backend to MSSS. MS supply some tools which I believe work with simple databases but I believe have problems with Nulls, funny (invalid) dates that Access accepts but MSSS will not, Yes/No fields ('cos Access can be Yes/No/Null). Every table must have a unique index (whether you use it or not e.g. you may be selecting SalesOrderDetails by a SalesOrder number and a Line number as a compound key. Well MSSS will want a unique ID field for it's internal optimisation)There is a list of other Gotchas which I can't remember and I'm not sure that the latest version of the MS tool accomodates them, but if you Google 'Access Upsizing' you will find some commercial tools that are a MUST to help you resolve these issues. We are not allowed to advertise here so I cannot give a direct link.

The website I am thinking of has videos to explain the issues and show how it is done.

Just holding the data in a SQL Server database will not neccessarily automatically improve response times. If you have tables with hundreds of thousands of rows, then Access will still demand all the data over the wire to show the one record you want. In these cases you may have to revise your form to send the criteria to MSSS to execute the query on the server so just the rows you want are sent over the wire.

Queries on the server are called Views. The commercial tool I use has options to extract all the queries out of the forms and write them as Views on the server.

I have users with Access front ends accessing data on MS SQL Server over a WAN hundreds of miles apart, so I know it works.

I also have IT departments telling me they do not want Access on the desktop (to stop users developing their own apps!)So I am exploring the use of Web Forms with a SQL server backend. The same stable supply tools to assist with that, but I have been lazy and not got them working for me yet!

I hope that is a start and will be interested to read the experience of others.
Jan 13 '12 #2

Expert Mod 15k+
P: 31,308
Just holding the data in a SQL Server database will not neccessarily automatically improve response times. If you have tables with hundreds of thousands of rows, then Access will still demand all the data over the wire to show the one record you want. In these cases you may have to revise your form to send the criteria to MSSS to execute the query on the server so just the rows you want are sent over the wire.
This is a very helpful answer. If I pick at this one paragraph then it it only shows the quality of the rest by contrast.

I don't believe this is generally true. My understanding is that Access (Jet) determines (not necessarily 100% successfully mind) what it can send in the way of criteria to the BE server. Many situations are thus optmised so that very little of the data need pass over the wire. Pass-thru queries are also possible where literally all the SQL is passed to the BE, but in such cases the SQL must be in the correct native format for the BE server. Nevertheless, there are situations where such optimasations are not possible, or are only minimally effective, and these can result in large volumes of data being transferred. This is a situation to avoid where at all possible for obvious reasons.

If I can be allowed to advise on your own position without causing any offense, my estimation of your database abilities is that you would struggle with the BE database systems other than Access. They tend to be considerably more involved than Access, and you seem to be in the process of getting to grips with Access.

Access is also generally agreed to be the quickest approach for getting ideas to a working project. Many serious developers still consider this is the most appropriate way to provide the FE functionality. Some disagree, and S7's point about giving such a tool to end users is often involved with such advice, but none can offer an alternative that's as quick to develop in.

Therefore, for you in your situation, I'd suggest getting to grips with the proper BE system of your choice for the data storage (which will be a big enough jump in itself), but continue to hone your skills, understanding and code-base in Access still.

Good luck whatever you decide though :-)

Jan 13 '12 #3

P: 393
If you define what you mean by 'limitations', it would be easier to help.

In any case, I've used both Access and SQL server as backends, after maintaining various databases on both, I've recently converted all of them over to SQL Server. All the front-ends are still in Access because there is no reason to retire Access as a front-end application. The pre-built functionality will save you gobs of time.

Now, the reasons I moved the backends varied. Some issues were limitations of Access, some were performance, and some were just because it was easier for me to manage the various applications within a single type of environment.

Access had a 2-4 GB limit depending on version, and I was exceeding that easily in some instances. Access backends also tend to struggle when more than 5-6 users are in there at the same time. I also inherited the maintenance of numerous Access DB's that were essentially storing the same data, but had different values depending on the last time it was updated.

Moving the BE's to SQL Server solved all of these issues.
All of that said, it's important to understand that SQL Server is still just a software program like Access. I work for a very large company and my SQL Server database is on a machine that is dedicated to acting only as a SQL server and has 26 processors. If you load it on single processor machine in your office...well, who knows how it will perform. Probably about the same as Access.

As for pulling data, there is no reason you can't use Access. The trick, as NeoPa pointed out, is to use Pass-through queries. By doing this, the SQL Server will process the query and just send results. I have queries written like this that are based on a half million records, are stored in a DB that's a hundred miles from me, and return results in under 1 second. The only caveats are that you must learn the SQL syntax for SQL server and if you have an functions that you use in your Access queries, you must either find their SQL Server equivalent or write them yourself on the SQL server. It's worth learning even it sounds painful.

If you choose not to use Pass-through queries, Access doesn't necessarily request all records as some one stated. Anything in your "WHERE" clause is usually processed server side. Avoid using the "HAVING" clause on your "GROUP BY" fields because that will almost certainly be processed on your side.

Ok, that's a bit more long-winded than I intended so I'll leave it at that. If you have any specific questions, I'll help if I can.
Jan 13 '12 #4

P: 78
Thank you both for your explanations.

I'm planning to create a new BE because our access BE is getting close to the is size limit. We will need to more data storage than the 2GB limit set by access. On a side note, would it be wise or practical to split the access BE by each table? I understand that transitioning will be a great challenge. I'm sure transitioning into SQL server would be a less intensive project. However, cost is an important factor to consider.


I never take offence to your comments. I know you are stating the facts in thoughts for the common good. Your opinion and solutions, as always, are appreciated and respected.


Thank you for the excellent overview. It was a great evaluation.


The access database is going to max out at about 2,000 records approx 1MB per record. The records contain details on properties used in the appraisal business. The records also contain pictures and pdf files (Attachment datatype). I'm not sold on SQL server or mySQL. mySQL is more attractive in regards to total cost of ownership.
Jan 13 '12 #5

P: 393
Split the BE by each table? What does that mean?
Jan 13 '12 #6

P: 393
I think a better approach for your pics and pdfs would be to store them in file folders and simply store the text location of them. Your front-end forms could reference the files and link to them programmatically.
Jan 13 '12 #7

P: 78

The pictures files need to be viewed on the reports/forms. It could be a possibility to link the pdfs outside of the access database with links.

Could you provide a small sample for such a procedure? If it would be more appropriate to start a new thread for it, I will. Just let me know.

I meant to say "Split the Back End of my current access project into several back ends with one table per back end linked together."
Jan 13 '12 #8

Expert Mod 15k+
P: 31,308
Split the BE by each table? What does that mean?
The limits of an individual Access database is approximately 2GB. If the BE (concept as a whole) had ten tables, each of 1GB each, then this could not work with a single Access BE database. However, if each table were stored in a separate BE database (Not always practicable but can be in some limited situations) then the FE could link to multiple tables store in multiple BE databases. Ten databases of approx 1GB each would not blow any limits.

There are various limitations to this but it can work in principle. The limitations are mainly to do with the inability to define relationships between tables across separate databases.
Jan 13 '12 #9

Expert 100+
P: 446
I guess this will get moved to a new thread but in essence to link to external PDF documents you will need to create a Hyperlink field to hold the hyperlink reference of each document.

You can have multiple hyperlink fields on one record (presumably each for a different purpose e.g. Specification, Survey Report etc) but if you wanted multiple documents for the same purpose e.g. Quarterly Review Reports, then you would have to link these in a subordinate table.

You can populate each hyperlink with a command button that opens up a file-picker dialogue box using the following code;
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdLink_Click()
  2. On Error GoTo Err_cmdLink_Click
  3. 'First, set a Reference to the Microsoft Office XX.X Object Library
  5. Dim strButtonCaption As String, strDialogTitle As String
  6. Dim strHyperlinkFile As String
  7. Dim varItem As Variant
  8. Dim strPath As String
  10. 'Define your own Captions if necessary
  11. strButtonCaption = "Save Hyperlink"
  12. strDialogTitle = "Select File to Create Hyperlink to Certificate"
  13. strPath = DLookup("[Address]", "My Company Information", "[SetupID]=2")
  15. With Application.FileDialog(msoFileDialogFilePicker)
  16.   With .Filters
  17.     .Clear
  18.     .Add "All Files", "*.*"     'Allow ALL File types
  19.   End With
  20.   'The Show Method returns True if 1 or more files are selected
  21.     .AllowMultiSelect = False       'Critical Line
  22.     .FilterIndex = 1 'Database files
  23.     .ButtonName = strButtonCaption
  24.     .InitialFileName = strPath  'vbNullString
  25.     .InitialView = msoFileDialogViewDetails     'Detailed View
  26.     .Title = strDialogTitle
  27.   If .Show Then
  28.     For Each varItem In .SelectedItems 'There will only be 1
  29.         Me!ProcURL = Me!CalibProc & "#" & varItem
  30.     Next varItem
  31.   End If
  32. End With
At Line #29 the URL to a Calibration Procedure document is is stored in the ProcURL field for the description held in field CalibProc.

To open the document for viewing you need a click event that runs the following;
Expand|Select|Wrap|Line Numbers
  1. Dim strInput As String
  2. strInput = HyperlinkPart(Me!ProcURL, 2)
  3. Application.FollowHyperlink strInput, , True
So long as there is a file association to an application that will display the document, then Automation will fire it up and show it.

Jan 14 '12 #10

P: 393
Well without getting into too much detail, here's the gist of how I would do it without storing the pic in the DB.

1.Create a file folder to store the pics. I'll use "\\MyNetwork\Pics" for this example.

2.Name the pics based on the UniqueID of the property. So if a property's ID is 24598 for example, then the pic would be "24598.jpg" (or some other picture format). I would also keep a picture as a default that I simply call "Default.jpg"

Assume the the picture control on the form is called PropPic and the Unique Id is called PropKey

3. The OnCurrent event for the for would be this:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2. On Error GoTo setDefault
  3. Me.PropPic.Picture= "\\MyNetwork\Pics\" & Me.PropKey & ".jpg"
  4. Exit Sub
  5. setDefault:
  6. Me.PropPic.Picture= "\\MyNetwork\Pics\Default.jpg"
  7. End Sub
I've never done it with a PDF, but you could probably use the same concept. Also, using error handling in this manner is probably not best practice.

Hope this helps.
Jan 18 '12 #11

Expert 100+
P: 446
Hi again,
There are a couple of issues here. dsatino is correct in the method to display a picture file in a form but the picture size is limited to the area available in the form, i.e difficult to inspect full size.

Unfortunately he is wrong to suggest that a PDF file can be displayed similarly. You must launch a PDF viewer and the hyperlink method achieves this.

Similar comments apply to link to Word or Excel documents.

The attached demo system shows how this works. It should be unzipped into a folder called c:\NF

The "Add Document" button at the foot of the form opens up a FilePicker dialogue box. Currently the associated files are all in one folder (C:\NF) but they could be in any folder.

Both the Path and the Hyperlink are stored in the database, plus a document title which becoimes part of the hyperlink. If the file-type is a JPG or BMP then the image will be displayed in the image control, as per dsatino.


You need a reference to Microsoft Office for the FilePicker dialogue box to function.
Attached Files
File Type: zip (563.9 KB, 56 views)
Jan 19 '12 #12

Post your reply

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