Sql Server BE/ Ms Access FE

[wizzz_wizzz]

Hi All.

I have recently split my MS Access DB so am having Sql Server BE/ Ms Access FE.

As the users running MS Access FE do not have good knowledge in creating complicated queries,
is there a good way to sync/update/pushout queries (changes) or new queries to
each MS Access FE users from the SQL server? Or is there a better way to "Push" queries to
MS Access FE?

[June7]

SQL is not a database, it is a language. Do you mean SQLServer is backend?

When FE is modified with new objects, have to distribute new copy of revised file to each user.

[Micron]

That is a common topic and there are many methods that can be implemented. Suggest you research and try one that fits your requirements and resources. I think the worst method would be to try to overwrite files with a batch or command file script since if they're open, the file won't overwrite with the new fe.

[wizzz_wizzz]

SQL is not a database, it is a language. Do you mean SQLServer is backend?

When FE is modified with new objects, have to distribute new copy of revised file to each user.

Yes, I meant SQL Server backend. Thank you for pointing out. I have edited my post to mean SQL Server, but unable to edit the Thread Title.

[pbaldy]

Yes, I meant SQL Server backend. Thank you for pointing out. I have edited my post to mean SQL Server, but unable to edit the Thread Title.

I've edited the title for you.

[wizzz_wizzz]

That is a common topic and there are many methods that can be implemented. Suggest you research and try one that fits your requirements and resources. I think the worst method would be to try to overwrite files with a batch or command file script since if they're open, the file won't overwrite with the new fe.

Hi Micron, thank you for pointing out that it is a common topic. As I have previously used the wrong terms to search = "Updating queries","sync", "push".....it always showed unwanted results. Learnt from June7's reply, "distribute" is the correct term to search for the desired results....am reading through them now. Cheers...

[Micron]

Two methods that I think are among the best:
1) user uses a shortcut that simply downloads the current fe. If it's the same one as yesterday, the so be it.
2) user opens db; code checks be table for version number that developer updates. If different, user has to download manually. If they don't, they're going nowhere.
Note - I'm not saying those are the top two, just two decent methods.

[wizzz_wizzz]

Two methods that I think are among the best:
1) user uses a shortcut that simply downloads the current fe. If it's the same one as yesterday, the so be it.
2) user opens db; code checks be table for version number that developer updates. If different, user has to download manually. If they don't, they're going nowhere.
Note - I'm not saying those are the top two, just two decent methods.

Thank you for sharing your views. It definitely helps me in choosing which way to go.

[Welshgasman]

Two methods that I think are among the best:
1) user uses a shortcut that simply downloads the current fe. If it's the same one as yesterday, the so be it.
2) user opens db; code checks be table for version number that developer updates. If different, user has to download manually. If they don't, they're going nowhere.
Note - I'm not saying those are the top two, just two decent methods.

I used a 3rd version which was an automated version of 2. I used Bob Larsen's Auto Updater.

Option one is the easiest to implement, but I have a thing about doing something for no reason. , hence I went with option 3. However this was for program changes which happened not that often, as compared to adding a new qauey each day or so. Once implemented it works by itself. User will need the automated version installed manually for the very first time.

Horses for course I guess.

I believe Gicu has an update process as well? http://forestbyte.com/ms-access-util...G3a5bsYECzLvNI

[Minty]

With SQL back end there is an alternative path, but it takes a little setting up.
I use a reporting table (lets be rogue and call it tblReports!)

In this I store various fields including the SQL for the query and whether dates etc are used as parameters.
I then use this data to drive a generic routine in the DB that builds a query based on this table.

These queries are run from a form that lists a User friendly name for the report/query and an option to directly export to a basically formatted Excel spreadsheet, or simply view read only on screen.

I can now add to the table from SSMS anything the client wants in terms of a query ( I use views on the server) and it magically becomes available to the end user without any update as it's all data driver from the reports table.
No front end update required. I can use this technique with parameterised stored procedures or filter views within access as I build the query on the fly based on the various setting in the table.

I simply add data to the reports table.

Obviously if they need something more opulent than a simple query output, like a cleverly formatted excel sheet, that normally requires a specific function, however I am working on a method of making the process more flexible and table data driven as well, but someone is paying me to do that so I'm afraid it won't be getting shared here.

[June7]

Following is a procedure I used to use to assure user has latest version of FE. Unfortunately, file copy method quit working after new computers set up and IT permissions no longer allowed programmatic copy of files. Had to modify to just open network folder location of new file for user to manually copy to local C:\ location.

Private Sub Form_Load()
        
'Check for updates to the program on start up - if values don't match then there is a later version
If Me.tbxVersion <> Me.lblVersion.Caption Then
    'because administrator opens the master development copy, only run this for non-administrator users
    If DLookup("Permissions", "Users", "UserNetworkID='" & Environ("UserName") & "'") <> "admin" Then
        'copy Access file
        CreateObject("Scripting.FileSystemObject").CopyFile _
            gstrBasePath & "Program\Install\MaterialsDatabase.accdb", "c:\", True
        'allow enough time for file to completely copy before opening
        Dim Start As Double
        Start = Timer
        While Timer < Start + 3
            DoEvents
        Wend
        'load new version - SysCmd function gets the Access executable file path
        'Shell function requires literal quote marks in the target filename string argument, apostrophe delimiters fail, hence the quadrupled quote marks
        Shell SysCmd(acSysCmdAccessDir) & "MSAccess.exe " & """" & CurrentProject.FullName & """", vbNormalFocus
        'close current file
        DoCmd.Quit
    End If
Else
    'tbxVersion available only to administrator to update version number in Updates table
    Me.tbxVersion.Visible = False
    Call UserLogin
End If


End Sub


Private Sub tbxUser_AfterUpdate()
If Me.tbxUser Like "[A-z][A-z][A-z]" Or Me.tbxUser Like "[A-z][A-z]" Then
    CurrentDb.Execute "INSERT INTO Users(UserNetworkID, UserInitials, Permissions) VALUES('" & VBA.Environ("UserName") & "', '" & UCase(Me.tbxUser) & "', 'staff')"
    Call UserLogin
Else
    MsgBox "Not an appropriate entry.", vbApplicationModal, "EntryError"
End If
End Sub


Private Sub UserLogin()
Me.tbxUser = DLookup("UserInitials", "Users", "UserNetworkID='" & Environ("UserName") & "'")
If Not IsNull(Me.tbxUser) Then
    CurrentDb.Execute "UPDATE Users SET ComputerName='" & VBA.Environ("ComputerName") & "' WHERE UserInitials='" & Me.tbxUser & "'"
    DoCmd.OpenForm "Menu", acNormal, , "UserInitials='" & Me.tbxUser & "'", , acWindowNormal
    DoCmd.Close acForm, Me.Name, acSaveNo
End If
End Sub

[NoellaG]

Hi,

as a SQL server I would go for the Minty suggestion in post #10. Badly written queries can be a real performance killer and the Access way to write queries is not really a good way to proceed. So I would suggest to write some SQl performant queries and/or views on SQL server itself, using the correct indexes, being careful with table functions, and generally following the rules for SQL server not access. Than make these objects available for your access users. This way the SQL DBA won't kill your SPID's, or worse, come for you with a blunt knife and a murderous grin on her face.