Access as FE and MS SQL Server as BE
-
Hi Guys,
I moved Access back-end (split database with FE and BE in Access) into MS SQL Server database using Sql server migration assistant.
In ms access i have now linked table:
Problem is that my tables do not have relationships in MS Access FE after migration.
Question is how to create these relationships in MS SQL Server?
Or maybe these relationships are already in MS SQL Server migrated?Please help,
Jacek - jaryszek - Wednesday, December 13, 2017 6:57 AMHi Guys,
I moved Access back-end (split database with FE and BE in Access) into MS SQL Server database using Sql server migration assistant.
In ms access i have now linked table:
Problem is that my tables do not have relationships in MS Access FE after migration.
Question is how to create these relationships in MS SQL Server?
Or maybe these relationships are already in MS SQL Server migrated?Please help,
JacekThe tables exist in SQL Server so that is where you would look for the relationships. You'll need to check the SQL Server instances to see f they are there.
If needed, you can find information on creating relationships for SQL Server in the following documentation:
Create Foreign Key RelationshipsSue
-
Thank you Sue_H!
I created relationships using Table designer and this is working fine 🙂
Best Wishes,
Jacek -
Hi Guys,
relationships where moved all together into MS SQL Server so it is great!
.
Problem is that MS Access in FE doesnt see my relationships from MS SQL Server...
How can i solve the problem?Best wishes,
Jacek -
Just as an aside... linked tables and bound forms etc. are really slow, you might want to look at using ado instead, more code but much faster.
-
Hi markdh.climb.
Thank you,
could you please explain more what do you mean saying using ado instead? Maybe you can give an example?I read that in Access FE you have to linked once more time your tables.
Please help Guys,
Warm Regards,
Jacek -
Basically you can take a bespoke approach or take more time and go generic.
The steps are simple but note that if you want to display the data grid control with multiple rows etc. you might want to write the rows to an access table local to the front end.
- create a stored procedure to return the data you want to display on the form and assign appropriate permissions (either return output parameters or a row set as you prefer).
- build the form without a data source.
- open a record set, in the forms vba module, against the stored procedure created above. See https://support.microsoft.com/en-us/help/168336/how-to-open-ado-connection-and-recordset-objects (use a client side cursor in the recordset if you want to be able to move freely back and forth through the rows).
This example shows you how to connect the record set to the server and then read the stored procedure. If you are displaying one record at a time you could even use a simple ado command and return the data as output parameters from the stored procedure instead of rows.
- Populate the form by
— check record set has row(S)
— if yes then write the field values in the rs to the forms text boxes etc. Or if using a data grid write them to the local table. See recordset methods and properties rs.movefirst, rs.movelast, rs.eof, rs.close, rs(“fieldName”), rs.fields etc.
— close the record set.
========
- save the updated data back to sql server
Important: binding forms sucks totally for performance and is buggy/confusing as hell in respect to handling data change events etc. Not wanting to sound controversial but, in my experience, only people not comfortable writing code would argue for binding to remote data sources. It may appear confusing but once you get your head around it it’s easy as you like.
Also Important: you will be working in a disconnected state, drop the record set completely (too many open connections etc. can slow things down), when the user saves the record you will need to write the data changes back to sql server with vba. I tend to do this by executing stored procedures with input parameters by passing the inputs as ado parameters in an ado command object (note that you need to create a reference to the ado library in the Forms code module, and that the full library prefix is adodb. E.g. adodb.recordset, adodb.command, adodb.connection).
That also means handling data validation at the frontend.
There are loads of examples on google and if you’re new to frontend code, vba etc., once you’ve nailed it you’ll find it extremely easy.
FYI I worked somewhere for years where we had the resource to test every approach, this is indeed the best in respect to performance and stability.... However, this process description is relatively high level, there are many ways to achieve a good end result using the above. Note also that you can reproduce a data grid in an access form using rows of text boxes etc. But, the data grid, when bound to a local table provide much more functionality in respect to sorting, filtering, changing column widths etc.
Have fun ;o)
-
Hi markdh.climb,
wow thank you for your answer!
I am using ADO connection in my FE often for example in Excel applications 🙂So it is very interesting challenge for me.
if yes then write the field values in the rs to the forms text boxes etc.
It is ok and it would be not a problem.
I can open one connection and set up recordsources for all comboboxes, textboxes etc. using MS SQL Server tables.Or if using a data grid write them to the local table.
This is very interesting. Data Grid does not exists in Access 2010.
What can i use here?
Furthermore if you have local table and deleting data from it - your Access FE database can be robust to much.
Please explain it in more detailsI tend to do this by executing stored procedures with input parameters by passing the inputs as ado parameters in an ado command object
Nice approach. Can you please give an example of your code for this?
Maybe something like this?Dim cn as ADODB.Connection, rs as ADODB.Recordset, cm as ADODB.Command
Dim strSQL as String, strName as StringstrName = “Smith’s Plumbing”
intUniqueKey = 12Set cn = New ADODB.Connection
cn.Open ConnStr() ‘I have a function ConnStr with my ADO connection string
strSQL = “Update tblMyTable Set fldName = ? Where mytable_uno = ?” ‘note the question marks.Set cm = New ADODB.Command
cm.CommandType = adCmdText
cm.CommandText = strSQLupd‘create an array of the values to replace the question makes in the same order as they appear in the SQL statement.
aryParms = Array(strName, intUniqueKey)‘run the execute command and the second parameter of the method is the array created above.
Set rs = cm.Execute(,aryParms)
Set rs = Nothing
Set cm = Nothing
Set cn = Nothing -
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.
-
Also, remember to close all connections and recordsets to avoid leaving orphaned connections. Close before setting to nothing.
-
Thank you very much markdh.climb!
I would, for smaller datasets, keep the last saved/original values in an array for comparison
What do you mean by this ?
Please give an example 🙂I do not exactly understand what do you mean here:
In access you can easily create forms programmatically as well as set att the properties and set the forms code modules text.
Why to create new forms automatically?
I can have one stable form and simple read recordset from MS SQL Server table.Maybe if user wants to save new records a new form can be opened with not null audit fields?
- for forms displaying only one record: use ado commands to both read and write.
There is no plenty of code in internet for this. How to do it via command? It is something like this?Dim oConn As ObjectSet 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 As ObjectSet cmd = CreateObject("ADODB.Command")
With cmd
Set .ActiveConnection = oConn .CommandType = adCmdText
.CommandText = "CALL my_procedure(?)"
.Prepared = True
.Parameters.Append .CreateParameter(, adInteger, adParamInput, , lngPartID)
End WithWhat is about in here?:
the stored procedure returns a single delimited string containing the rows.
So from MS SQL Server you have for example Array: {1, Smith, John, 2, Skywalker, Luke} as string.And now you are populating table using loop to split array?
or with all data as a delimited string that can be passed into a table variable
Can you provide example for this?adodb command either once per row, or in batches where there are enough parameters to handle multiple rows
Please give an example, it is simpler to understand this.Thank you my Friend for your help and support
Best Wishes,
Jacek -
@Jacek
http://utteraccess.com/forum/________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day -
J Livingston SQL this is not helping.
I have to fully understand the whole topic and now i do not exactly what should i look for.
Additionally my question is regarding different aspects including MS SQL Server.
Please help,
Jacek -
Hi,
I move the topic to another forum (Access forum):
http://www.accessforums.net/showthread.php?t=69646&p=381414#post381414Best wishes,
Jacek
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply