Hi,l
I am about to start a new development that will ultimately have to migrate to SQLServer or something similar.
Are there any difficulties or roadblocks associated with doing the development using Access with SQLServer from the beginning?
Decide first whether you are going to connect to sql server using DAO or ADO (although you can do both if relevant). DAO typically means you will be using linked tables, ADO means you will be creating recordsets as required and assigning them to form/combo/listbox recordsets. Performance wise, no real difference. The issue with ADO is that the standard form functionality for sorting and filtering will not work - you will need to write your own sort/filter shortcut menus. And you cannot use ADO recordsets for reports.
A benefit of ADO is you can disconnect from the recordsource once populated. Useful if you have lots of connections or don't want to use temporary tables. Not a problem for snapshot type recordsets, but means you need to build a routine to pass changes/additions back to the server if it is required to be updateable. I often use them as an alternative to arrays/collections/dictionaries when it is just data.
So really depends on what the app and its and the processes are required to do.
Also be aware that sql server may be slow compared with an access BE - it depends on network connections to the server and how it is configured to provide the resources required. So ensure all data processing is handled in sql server and not passed back to Access to process - so for example don't base forms on tables, base them on queries with value criteria to reduce the data flow over the network (so not SELECT.... WHERE ID=forms!form1.txtID, use SELECT.... WHERE ID=1 ) . Do not use domain functions (Dsum, Dcount, etc).
Hi Guys,
Thank you for the help.
DBGuy... nice to see you are still around after all this time. (You might remember me as Alan from Malawi on Utter Access)
CJ London. Thanks for the detailed response. I really appreciate it.
As there are never likely to be more than 7 or 8 users, it would seem to me that DAO would be less hassle. Am I right?
depends on what the app is doing. DAO is probably easier since the form sort/filter actions will work out of the box. But it is potentially less secure if you don't tie down who can see what in terms of the back view - tables, queries etc (i.e. hide that access navigation window). You should never allow users direct access to these.
depends on what the app is doing. DAO is probably easier since the form sort/filter actions will work out of the box. But it is potentially less secure if you don't tie down who can see what in terms of the back view - tables, queries etc (i.e. hide that access navigation window). You should never allow users direct access to these.
You are absolutely correct Fortunately the client,in this case, is a small non profit organization managing a retirement home. They are pretty unsophisticated in IT terms. I am doing this project for them at no cost as their funds are very limited. Nevertheless, I have always hidden tables etc away from users as I am afraid of the damage that stupid people can do.
Also I have always used the Access Run Time and ensured that the end users do not have Access itself on the user terminals
Considering your situation, I'd say go with DAO first and see how it goes. Consider switching to ADO for specific beneficial reasons at that point and make a decision from there. I wouldn't start trying it with ADO and doing all that on the fly to begin with.
You are absolutely correct Fortunately the client,in this case, is a small non profit organization managing a retirement home. They are pretty unsophisticated in IT terms. I am doing this project for them at no cost as their funds are very limited. Nevertheless, I have always hidden tables etc away from users as I am afraid of the damage that stupid people can do.
Also I have always used the Access Run Time and ensured that the end users do not have Access itself on the user terminals
Considering your situation, I would keep it very simple:
* SQL Server Express, the free edition, on-prem. You'll need to add a sproc to perform a database backup, since scheduled backups are not included in Express.
* Very few stored procedures and views. They are mostly to optimize performance and security, but that is not an issue here. Use linked tables and Access queries.
* Focus mostly on simple and consistent UI with solid validation code. Simple workflows. Make it accessible.
I agree with the others to move forward with starting with SQL Server from the start of development. You note that the project will ultimately have to migrate. I'm curious about the reasons or thought process behind that. For example, do you expect the database to grow in size significantly over time, do you need the enhanced security for your backend, higher performance in query processing, etc. These things can help in deciding between these other things that are being raised in this thread -- such as whether to use ADO/DAO, stored procedures/views, etc.
Hi,l
I am about to start a new development that will ultimately have to migrate to SQLServer or something similar.
Are there any difficulties or roadblocks associated with doing the development using Access with SQLServer from the beginning?
Actually, you are 100% MUCH better off to start out using SQL server from day one.
Since you noted you have "run time" experience, then I think you have a good grasp of the development cycle here, and I assume you also are aware of the split front end, and back end setup.
However, when JUST starting out? And depending on how comfortable you are with SQL server and SSMS?
You can VERY well make the case that when JUST starting out?
You build your tables in Access - and your application not even split yet, right?
I have often done the above, since local access table designs REALLY helps you get up and running fast.
Once you have the basic tables outlined, then like all developers, note how the table changes (and adding of tables) settles down to a "dull roar".
Once that flurry of new tables and designs "settles" down to a dull roar?
Then I recommend you split the database.
Then I recommend you migrate the back end to SQL server.
Now, I only and I REALLY mean this:
Only do above if you are really but REALLY comfortable with the SSMAA
(SQL server migration assistant for Access).
If you are not 100%, or 200% comfortable and have not used SSMAA a lot?
Then probably best for you to just start out 100% using SSMS to build the tables in SQL server, and then as noted link the tables from Access as you go along.
I assume that you have:
A good re-link utility that will re-link the tables in VBA.
I also suggest adding a super tiny one-liner sub that allows to to do this from the command line:
Code:
LinkOneTable "tblCustomers", "dbo_Customers"
I use the above to setup a new linked table to SQL server - it's really quick/fast, and MUCH less work then say having to fire up the linked table manager.
I also have a one liner called "linkDB", that I can again run from Access debug window when I JUST made a table change.
(since after a SQL server table design change, you have to re-link those tables in Access).
So, actually, it not all that bad of a idea to start out un-split (like oh so many of us developers have done in the past). This gets your basic tables (and relationships etc.) setup with the least efforts and hassle. it's fast, easy, and probably why we all love Access so much!
Then, as noted, probably best to split the database.
Then, as noted, migrate the back end to SQL server, and then re-link the front end.
(I even have some VBA code to take the existing linked tables to the accDB back end, and flip them over to SQL server links).
As noted, the above idea (working local, and un-split)?
it's a great road and idea, but this advice only works well if you REALLY comfortable with SSMAA - and sometimes that program is a absolute "bear" to ride and whack and get working correctly for you. And I still had some stability issues with SSMAA.
However, if not really used to SSMAA? Then I think you better off to start day one using SQL server and SSMS for your tables.
And I do not recommend at all bothering with, or introducing ADO into your application.
Actually, you are 100% MUCH better off to start out using SQL server from day one.
Since you noted you have "run time" experience, then I think you have a good grasp of the development cycle here, and I assume you also are aware of the split front end, and back end setup.
However, when JUST starting out? And depending on how comfortable you are with SQL server and SSMS?
You can VERY well make the case that when JUST starting out?
You build your tables in Access - and your application not even split yet, right?
I have often done the above, since local access table designs REALLY helps you get up and running fast.
Once you have the basic tables outlined, then like all developers, note how the table changes (and adding of tables) settles down to a "dull roar".
Once that flurry of new tables and designs "settles" down to a dull roar?
Then I recommend you split the database.
Then I recommend you migrate the back end to SQL server.
Now, I only and I REALLY mean this:
Only do above if you are really but REALLY comfortable with the SSMAA
(SQL server migration assistant for Access).
If you are not 100%, or 200% comfortable and have not used SSMAA a lot?
Then probably best for you to just start out 100% using SSMS to build the tables in SQL server, and then as noted link the tables from Access as you go along.
I assume that you have:
A good re-link utility that will re-link the tables in VBA.
I also suggest adding a super tiny one-liner sub that allows to to do this from the command line:
Code:
LinkOneTable "tblCustomers", "dbo_Customers"
I use the above to setup a new linked table to SQL server - it's really quick/fast, and MUCH less work then say having to fire up the linked table manager.
I also have a one liner called "linkDB", that I can again run from Access debug window when I JUST made a table change.
(since after a SQL server table design change, you have to re-link those tables in Access).
So, actually, it not all that bad of a idea to start out un-split (like oh so many of us developers have done in the past). This gets your basic tables (and relationships etc.) setup with the least efforts and hassle. it's fast, easy, and probably why we all love Access so much!
Then, as noted, probably best to split the database.
Then, as noted, migrate the back end to SQL server, and then re-link the front end.
(I even have some VBA code to take the existing linked tables to the accDB back end, and flip them over to SQL server links).
As noted, the above idea (working local, and un-split)?
it's a great road and idea, but this advice only works well if you REALLY comfortable with SSMAA - and sometimes that program is a absolute "bear" to ride and whack and get working correctly for you. And I still had some stability issues with SSMAA.
However, if not really used to SSMAA? Then I think you better off to start day one using SQL server and SSMS for your tables.
And I do not recommend at all bothering with, or introducing ADO into your application.
Hi Albert,
Thank you so much for all the effort you put into this reply.
However, when JUST starting out? And depending on how comfortable you are with SQL server and SSMS?
My answer to this statement is Not very comfortable at all. I have never worked with either
it's a great road and idea, but this advice only works well if you REALLY comfortable with SSMAA - and sometimes that program is a absolute "bear" to ride and whack and get working correctly for you. And I still had some stability issues with SSMAA.
Hi Dave,
Thank you for your response.
I have seen so many dire warnings about Access alone being poor at multi user networking. Almost all suggest using SQL server.
The database will never get significant in size or number of users.
I'm in same position; I actually have not completed my app fully in Access & moved to SQL Server asap & I'm glad I did because the control-flow/ processes are quite different rendering considerable redesign to work in SQL Server; the more experienced here will hopefully correct me if I'm misinforming you [apologies if so]:
Migrate Access To SSMS - see video, was buggy & I ended up creating some from scratch, with new data-types available in SQL Server; BigInt & TinyInt mostly & larger Decimal precision
Queries - struggled with the concept of what actually a Query is in SQL Server now as it's convoluted with most of below;
Views - closest to what would be a normal saved query in Access. Behaves like a table with calc'd columns
Stored Procedures (aka SP's/ SPROC's) - ... an Action Query/ Procedure in VBA/ Procedures similar to VBA; must use EXEC to run them
'New Query button' - Temporary queries & create do whatever you want in SQL Server
Dynamic SQL - Procedures similar to VBA/ looping; avoid if possible to
Common Table Expression (CTE) - can select info temporarily & easily reference with an alias to avoid superfluous references amongst other benefits, looping (haven't done yet)
I don't fully understand this since there is some to not use DSN (Data Source Name) if possible to avoid having to relink the BE in SQL Server. But it also seems that the DSN is merely a text file/ whatever that stores the Connection Properties & is not required on deployment at the FE when deploying
Do not 'Remember Password' as it is then stored in registry easy to hack; but as I learn more you'd probably be changing roles & assigning new Principals/ Roles but I have not done this yet but best practice not to 'Remember Password'
Db Prinicipals (Security) - inexperienced here - I'm probably wrong - User/ Group/ Process where Objects (Things In The Object Explorer) can be restricted. Supports hierarchial inheritance
App Roles (Security) - inexperienced here - I'm probably wrong - Principle is it's parent & it inherits permissions from the Prinicipal
Assembly - can store a .dll different language in here for function purposes to obfuscate math
One correction from me - you migrate access back end (tables and possibly queries from front end) to sql server, not access and not SSMS. SSMS is just the tool for managing sql server in the same way you would use access to manage access tables (and queries). You cannot use SSMS to create forms or reports for example to display or have users interact with the data, which is what access is about
You don't have to use SSMS to manage SQL server, there are plenty of other tools available.
One correction from me - you migrate access back end (tables and possibly queries from front end) to sql server, not access and not SSMS. SSMS is just the tool for managing sql server in the same way you would use access to manage access tables (and queries).
Thanks CJ, not sure that is entirely correct - the video linked was a migration to SQL Server using SSMA wizard (Microsoft Sql Server Migration Assistant); unique to only Microsoft Sql Server; not SQL Server. To generically state SQL Server here I believe would be incorrect as the Migration Wizard SSMA is unique & offered to only SSMS migration from MS Access. I could of course be wrong, but I think my defence has merit, sounds like I'm getting defensive; I don't care about short-term silliness; I care about mastering db design!
Thanks Ron, I know little of the subject with Access & only experienced from HTTPS certificates... Google says it is possible but most often is due to misconfiguration of the token. I know you have much more experience than me so I'll take your word.
Thanks Ron, I know little of the subject with Access & only experienced from HTTPS certificates... Google says it is possible but most often is due to misconfiguration of the token. I know you have much more experience than me so I'll take your word. View attachment 122814
I can't fine the original link but if you continue questioning your AI you will get to something like the following.
Connection Scope:
sp_setapprole applies only to the current connection.
If Access opens new connections (e.g., for linked tables), you may need to re-activate the role for each.
I did a presentation on https://accessusergroups.org/groups/pacific/ group about my process for emulating AD login to SQL Server using database account and cached connections. This came out of a Utteraccess post which is no longer available.
Some links:
Improve the Security of Database Connections:
When linking to external tables using Open Database Connectivity (ODBC), Access offers to set up a Data Source Name (DSN). The DSN is quite convenient, giving us a graphic method to quickly build and specify an ODBC connection string without having to remember all of the syntax and parameters...
Does anyone know if there is a way to use SQL Server Application Roles from within Ms Access? I have an Ms Access application with tables linked to SQL Server. Users of the application can access and manipulate data in the linked tables according to…
For many developers, database security and Access control is just something that gets in the way of development work. However, several recent security breaches have had devastating consequences and have caused a change in attitude about the value to any organisation of having database...
Does MySql 5.1 and SQL Server 2008 (Web edition, Standard) have any functional password limitations other than length limits? Are metacharacters in any form a bad idea to use, like bang, pipe, hash...
Please clarify what you mean by SQL Server. MS SQL Server is a RDMBS ( Relational Database Management System.) as is mySQL, PostGre, Oracle Database and many others. So far as I am aware there is no RDBMS simply called 'SQL Server', that term just refers to MS SQL Server
If you had said 'Migrate Access tables To SQL Server using SSMS', I would not have commented
One correction from me - you migrate access back end (tables and possibly queries from front end) to sql server, not access and not SSMS. SSMS is just the tool for managing sql server in the same way you would use access to manage access tables (and queries). You cannot use SSMS to create forms or reports for example to display or have users interact with the data, which is what access is about[/URL]
. Or maybe what is meant by the subforum's definition:
