SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Access as FE and MS SQL Server as BE


Access as FE and MS SQL Server as BE

Author
Message
jaryszek
jaryszek
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1025 Visits: 149
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
Sue_H
Sue_H
SSC Guru
SSC Guru (77K reputation)SSC Guru (77K reputation)SSC Guru (77K reputation)SSC Guru (77K reputation)SSC Guru (77K reputation)SSC Guru (77K reputation)SSC Guru (77K reputation)SSC Guru (77K reputation)

Group: General Forum Members
Points: 77286 Visits: 15517
jaryszek - Wednesday, December 13, 2017 6:57 AM
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


The 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 Relationships

Sue



jaryszek
jaryszek
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1025 Visits: 149
Thank you Sue_H!

I created relationships using Table designer and this is working fine Smile
Best Wishes,
Jacek
jaryszek
jaryszek
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1025 Visits: 149
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
markdh.climb
markdh.climb
SSC-Enthusiastic
SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)

Group: General Forum Members
Points: 146 Visits: 115
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.
jaryszek
jaryszek
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1025 Visits: 149
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
markdh.climb
markdh.climb
SSC-Enthusiastic
SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)

Group: General Forum Members
Points: 146 Visits: 115
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)
jaryszek
jaryszek
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1025 Visits: 149
Hi markdh.climb,

wow thank you for your answer!
I am using ADO connection in my FE often for example in Excel applications Smile

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 details

I 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 String

strName = “Smith’s Plumbing”
intUniqueKey = 12

Set 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


Thank you very much !!
Jacek


markdh.climb
markdh.climb
SSC-Enthusiastic
SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)

Group: General Forum Members
Points: 146 Visits: 115
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.
markdh.climb
markdh.climb
SSC-Enthusiastic
SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)

Group: General Forum Members
Points: 146 Visits: 115
Also, remember to close all connections and recordsets to avoid leaving orphaned connections. Close before setting to nothing.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum









































































































































































SQLServerCentral


Search