FE Access-BE SQL server: prerequisites (VBA, primary keys, queries)

[Etxezarreta]

Hello everyone,
I have developed an Access tool: 20 tables (all the primary keys are auto numbers), roughly 15 forms: the queries are included in the VBA code with SQL language.
I use several macros with arguments (to select-deselect all items in listboxes for instance) and one VBA class module.
There also will be a Excel file that will collect some data to build a powerpivot dashboard.
5 people will have to work on it, they live far from each other, they will have to use an internet connection. So I think the best way to do it is to put the tables in SQl server or MySQL.
I have read quite a lot, but I can't find clear answers, to the following questions:
- Do you agree with me, if not could you give me some details?
- Do you think the way I designed the Access tool will allow such a migration?
- What is the best solution between mySQl and SQL server?
Many thanks in advance, I am a bit lost at that moment, so I will be extremely happy to read your answers!
Etxe.

 

[theDBguy]

Hi Etxe. I don't think you've given us enough information to give a meaningful response. But for starters:

Q1: Yes, I agree with you. Using MySQL or SQL Server would allow Access users to connect to your data via the Internet
Q2: That would depend on how you designed your tool. The main difference between using an Access data file and a server over the Internet would come down to "speed." You would want to minimize network traffic to maximize the speed.
Q3: Can't which is the "best" solution. We don't know your background or your environment. Besides those are two different brands, they probably use different programming languages. They definitely use different data connection drivers.

Hope that helps a little...

 

[Etxezarreta]

Thanks a lot the DBguy.
I am not very used to sharing on my work, could you be more specific about the information that will help you help me?
I can send you a homemade "data model" I designed on Excel for instance? It shows with words how data are related.
Thanks again.
Etxe.

 

[theDBguy]

Thanks a lot the DBguy.
I am not very used to sharing on my work, could you be more specific about the information that will help you help me?
I can send you a homemade "data model" I designed on Excel for instance? It shows with words how data are related.
Thanks again.
Etxe.

Hi. The table structure is not so important when comparing the difference between using an Access FE with an Access BE versus an Access FE and a DBMS BE over the Internet. It's your FE's design that will determine how it will function (better or worse) when you switch to using it over the Internet. If I find a link for you to explain it better, I will. Cheers!

 

[Minty]

Trying to drag large recordsets over a WAN link is a recipe for disaster, so what you need to do is design your FE (regardless of the DBMS you plump on) to accommodate this fundamental issue.

If you want to play around with an example - sign up for a free developer Azure account create some tables, populate them and them create forms to interact with that basic data.
This will give you a good quality cloud-hosted BE that will let you discover the design issues at hand.

 

[Etxezarreta]

Hi Minty and theDBguy,
I have a few more questions:
- To make my point clearer, I will put the BE tables in SQL server, because I have very little data and few users (5). I am rewriting my code to use ADO instead of DAO, and to load all the forms, sub-forms and controls from procedures using SQl code.
Here are my 3 questions:
- 1 : quite general: could I post a sample of code , so that you can tell me if it is consistent with the way I designed my FE-BE articulation.
- 2 :more specific: when I want to test if my recordsets are populated, a error is generated by 'CurrentProject.connection', but it works fine with
'CurrentProject.AccessConnection'. (check below, part of the procedure on Form Load event)): why does it happen? Will it be a problem when I put the BE in SQL server?
- 3: linked with '1': please feel free to make comments on other aspects of those few VBA lines that could prevent a good linking with SQl server hosted data.
Thanks a lot.

'ListBox pour noms des projets
Set Me.combo_ListeProjetsAaffecterAuxContacts.Recordset = Nothing
Set objConnection = CurrentProject.AccessConnection

str_SQL_ProjetAffecte = "SELECT ID_Projet,Projet FROM t_Listeprojets ORDER BY Projet"
Set objRcdset_Source_Projet = New ADODB.Recordset
With objRcdset_Source_Projet
.Source = str_SQL_ProjetAffecte
.ActiveConnection = objConnection
'doesnt work with 'CurrentProject.connection
.CursorType = adOpenDynamic
.LockType = adLockOptimistic
.Open
If Not .RecordCount > 0 Then
MsgBox "Guttienez egitasmo bat sortu behar duk, bertzenaz ezin duk aintzinat gan", vbOKOnly
Exit Sub
End If
End With

Set Me.combo_ListeProjetsAaffecterAuxContacts.Recordset = objRcdset_Source_Projet