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

Access 2007 FE + SQL Server Express or MySQL BE?

100+
P: 547
My one application that deals with medicine records, may outgrow Access 2007 as a back-end in the next 6 months.

I am now starting to think about the possible migration, but is totally out of my depth regarding what to do and which one would be the best, to use.

What does it involve as i see words like mysql Connector/ODBC etc involved. Do i have to change my existing tables or queries, for it to work?

Any suggestions or planning or reading to do, taking into account my minimal experience.
Thx
Oct 29 '13 #1
Share this Question
Share on Google+
7 Replies


Seth Schrock
Expert 2.5K+
P: 2,931
I have found that it is extremely simple. I use SQL Server at work because we already we using it. What I do is to create a System DSN (done by going to Administrative Tools>Data Sources (ODBC) and choosing the System tab) and then in your external data choose ODBC and then find your data source. You then link to your tables and then Access sees them just like local tables.

One difference that I can think of is when working in VBA and trying to open a recordset, you have to add the dbSeeChanges option. For example, where you would normally use
Expand|Select|Wrap|Line Numbers
  1. Set rst = db.OpenRecordset("tableName", dbOpenDynaset)
you would instead use
Expand|Select|Wrap|Line Numbers
  1. Set rst = db.OpenRecordset("tableName", dbOpenDynaset, dbSeeChanges)
Nothing too big, but when I first was trying to move a database over, this caused me all kinds of headaches to try and figure out.

There are all kinds of instructions for DSN-less connections, but they seemed to be more headache then they were worth to me and it was so simple to setup a DSN I just took that route. I have even created my own .reg scripts to make it so that you can create your DSNs using a reg file on all your systems instead of having to type everything in for each system. Makes less room for error.
Oct 29 '13 #2

100+
P: 547
Thx Seth for that info. It is an option to consider.

I am currently using my information system here in rural South Africa in 40 health clinics, as a pilot system, and will soon have to expand to over 200 facilities in my region. Because we do not have data networks in these rural areas, we use memory cards to backup data, and restore it on my Access server weekly.
It is filling up rapidly now.
My only worry with MS SQL server express, is the 4gig limit i will have in the next year or so, as i do not want to migrate again. The other option would be to dump the old data into archive tables, i suppose.

I do not have a sponsor for the real SQL server and therefore has to get by with what i can lay my hands on.

I am just doing my homework for now. Has anybody got experience with MySQL and the difficulty and issues, when migrating from Access 2007?
Oct 29 '13 #3

zmbd
Expert Mod 5K+
P: 5,287
There are all kinds of instructions for DSN-less connections, but they seemed to be more headache then they were worth to me and it was so simple to setup a DSN I just took that route.
I agree; however, they have their uses too.
I will typically develop my front end using the linked tables and a DSN connection; however, many of my "client" lab PC's don't have the DSN configured, it's locked, and it takes an act of congress (USA) to get one created by IT. However, I can use the DSN-less connections to connect to the network SQL.
So, what I am saying is that it wouldn't be a bad thing to learn a bit about the DSN-less connections in the near future and concentrate on the easy development first. If you get stuck then you know where to come!
Oh, and one other thing, Pass thru queries (PTQ) are exactly that, ACC-Jet/ACE Passes these thru to the SQL server without any intpretation. The SQL-Server will NOT see the values on your form so any PTQ that you use usually will need to be resolved with the values from the form fields - even if these are from linked tables.
Oct 30 '13 #4

zmbd
Expert Mod 5K+
P: 5,287
@Neelsfer:
SQL Server Express is a free edition of SQL Server ideal for developing and powering desktop, web and small server applications. (SS2012)Take advantage of the same powerful database engine as the other versions of SQL Server in a version great for redistribution and embedding, free! SQL Server Express includes 10GB of storage per database, easy backup and restore functionality and compatibility with all editions of SQL Server and Windows Azure SQL Database.
Didn't see your concern about the 4GB limit... the express is 10GB and the other paid versions are virtually unlimited.

MYSQL is also an option you should explore.MySQL Technical Specifications

Then there is the ORACLE... sigh
Oct 30 '13 #5

100+
P: 547
thx zmbd for the info , the 10gig may work for me. I will start working on the sql server express this weekend, on a spare dedicated pc i have for this purpose. Its currently running on Windows 7. Will bug you if i experience issues, if you don't mind.
Oct 30 '13 #6

zmbd
Expert Mod 5K+
P: 5,287
Just remember... I get bogged down so if I don't answer right away please be patient.

Seth has been beating his head on these connections for awhile here too so he'll have some insight on how to solve your migraines (@_@)!
Oct 31 '13 #7

Seth Schrock
Expert 2.5K+
P: 2,931
Thankfully, I believe that I have worked out all the headaches that I had (including x86 vs x64 systems) so I'll be glad to help you.
Oct 31 '13 #8

Post your reply

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