Best efficient desgin - MS Access FE and MS SQL BE
Windows 7 32bit 
Access 2010 32bit
Hi,
i am trying to create the best design approach with MS Access as FE and MS SQL Server BE.
I am wrting about here on MS SQL Server forum here:
https://www.sqlservercentral.com/For...s-BE#bm1913427
Post number #1913403.
I want to ask you few questions which are more about Access then MS SQL Server.Code:Sorry, I was a little misleading when I said datagrid. What I meant is the data sheet view in an access form, I.e. you can bind a form, or certain objects (query for example) within a form to a local table via a sql statement. That table can either be in the frontend access file (controversial) or a separate access database file on the same drive as the FE (linked tables to FE). Either way, you can programmatically compact and repair at the end of the save routine, The table is a user specific work space on the users local drive. In this scenario you can bind efficiently (I.e. there is no lag for the user editing and searching data (as it’s local) but to save you must check for changes and then write them back. I would, for smaller datasets, keep the last saved/original values in an array for comparison, on larger data sets i’d have an updatedBy audit field in the local table, you can bind a column/text box to a function, or give default values when the form opens, and use the audit fields not null to filter for updates in the save. Also, you can buy third party data grids and embed them in forms, OCX, DLL etc, that are functionally much richer than the access data sheet view. There’s plenty to chose from. I’m not in front of a computer, using an IPad for this, so don’t have examples at hand. There’s plenty on google. Note that you should never create an application anywhere where you are passing dynamically created sql back to be executed by the server! All data access and manipulation should be via stored procedure and no user can access tables directly. Your approach would work but you are better off (mandatory) using stored procs as opposed to frontend sql statements. I wrote code once that would, based on a form template, create a fully disconnected access form with all code bespoke to the stored procedures that would handle the updates and reads. Basically build the template form with just the minimum objects etc. And then write a procedure, vba, to merge predefined vba, stored as data or a constant etc., with the detail from the table definition (which you should be able to read in a dev environment easily enough via ADO or DAO table or recordset definitions. In access you can easily create forms programmatically as well as set att the properties and set the forms code modules text. My general approach: - for forms displaying only one record: use ado commands to both read and write. - for forms with only a few records returned I usually do the same as above, but on the read, the stored procedure returns a single delimited string containing the rows. Important: command objects are better than recordsets. Let the front end delimit the string. I also use this for populating short lists on combo boxes etc. On the save I would save row by row mimicking the default data sheet behaviour in access (I.e. data is updated when the user moves to another row etc. - for larger datasets, read a recordset into a local mdb table, use and audit field to mark updated records and then when saving i’d use and adodb command either once per row, or in batches where there are enough parameters to handle multiple rows, or with all data as a delimited string that can be passed into a table variable or something in t-sql for smaller datasets (e.g. person title, month, day of week etc.). In some cases you might want to able to allow the user to specify when to save, generally for larger datasets, so they can either work off line, or simply review all changes before committing. I’ve done a few systems where the user could “book out” data, work on it remotely and then book it back in at a later date. The vba would roughly contain: - form open/load procedure to call read procedure etc. - read procedure - form population procedure - has changed function - validation function - save function - form exit function to check and warn about unsaved changes etc.
General approach building data model is use Access with no linking tables, only forms in Access and recordsouce from MS SQL Server using ADO/ADODB connections.
What author wants to do it here with array comparison?I would, for smaller datasets, keep the last saved/original values in an array for comparison, on larger data sets i’d have an updatedBy audit field in the local table,
Second option I understand because you can add dates when user inputting new data and you will have audit fields...
Are you, Access Experts using something like this? Can you give any example?
Also, you can buy third party data grids and embed them in forms, OCX, DLL etc, that are functionally much richer than the access data sheet view. There’s plenty to chose from.
Why to create new forms automatically?In access you can easily create forms programmatically as well asset att the properties andset the forms code modules text.
I can have one stable form and simple read recordset from MS SQL Server table.
here is no plenty of code in internet for this. How to do it via command? It is something like this?- for forms displaying only one record: use ado commands to both read and write.
Dim oConn AsObjectSet oConn =CreateObject("ADODB.Connection")
Const MYSQL_DRIVER ="{MySQL ODBC 5.1 Driver}"Const MYSQL_SERVER ="10.32.27.6"Const MYSQL_DATABASE ="sales"Const MYSQL_USER ="root"Const MYSQL_PASSWORD ="xxxxxx"oConn.ConnectionString="DRIVER="& MYSQL_DRIVER &";Server="& MYSQL_SERVER &";Database="& MYSQL_DATABASE &";Uid="& MYSQL_USER &";Pwd="& MYSQL_PASSWORD &";"oConn.Open' parameter valueDim lngPartID As LonglngPartID = 12345'
Create ADODB.CommandConst adCmdText =1Const adParamInput =1Const adInteger =3Dim cmd AsObjectSet cmd =CreateObject("ADODB.Command")
With cmd
Set.ActiveConnection= oConn .CommandType= adCmdText
.CommandText="CALL my_procedure(?)"
.Prepared=True
.Parameters.Append.CreateParameter(, adInteger, adParamInput,, lngPartID)
EndWith
fiu fiu here i dont know what to do...Maybe get array from MS SQL Server and split it to the table using loops?the stored procedure returns a single delimited string containing the rows.
Do you have any example for this?or with all data as a delimited string that can be passed into a table variable
Here i please for example...adodb command either once per row, or in batches where there are enough parameters to handle multiple rows
I will be very grateful for any tips or sample databases.
Bulding data models like here seems to me very exciting.
Thank you in advance,
Warm Regards,
Jacek Antek
Reply With Quote
In fact, is seemed even a little slower. All data was still being transferred across the LAN. 
Originally Posted by ssanfu
