Page 2 of 2 FirstFirst 12
Results 16 to 20 of 20
  1. #16
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    I've agreed with PKStormy that questions with potential public interest will continue to be dealt with in the forum thread
    So taking points from PK's last 2 posts together ...this is a very long answer!

    What made you explore using MS Access and SQL Server over something like .NET?
    I thought about learning .NET about 10 years ago but heard there a steep learning curve.
    As I had no real need for it, it was a good excuse to not do so

    Did you by chance get the chance to try the MS Access 'Star' feature prior to SQL Server days?
    I didn't start using Access until v97 and only became a serious user around 2000
    TBH that's the first I've heard of this 'Star' feature!

    I wonder what the code to import the SQL Server tables would look like using a DSN-Less connection.
    ....
    I'm hoping to modify the code to import SQL Server stored procedures instead of SQL Server tables to overcome big data size issues on tables that would cause the MS Access file to exceed it's 2 gig filesize limitations.
    Somewhat confused by these two comments.
    My SQL tables are LINKED to ACCESS FEs.
    Why would you IMPORT SQL tables rather than LINK them?
    Linking means there is no reason why the Access FE would ever reach the 2GB size limit

    I use this code in the example which is based on an ODBC Dsn.
    Dim cxnString As String
    cxnString = "ODBC;DSN=" & ODBCName & ";UID=" & UIDLogin & ";" & UIDPW & ";LANGUAGE=us_english;DATABASE=" & SQLDBName


    DoCmd.TransferDatabase acImport, "ODBC Database", cxnString, acTable, SQLTableName, SQLTableName

    If possible, could you translate the above to use a DSN-Less connection? It's been a while since I've worked with DSN-Less connections. I'm going to do some research on using them again.
    Sure. There are lots of similarities except the equivalent would be acLink not acImport
    The link details are held in 2 tables - tblTableLinkTypes & tblTableLinks
    The relink form below shows the details for one client school - each school will have at least 2 of the link types (starting with SDA) but if they have purchased one or more additional apps (CEO/SPS) could have those as well.
    The connection strings will of course differ for each client school depending on file location (Access) / SQL server details

    Click image for larger version. 

Name:	TableLinkTypes.PNG 
Views:	19 
Size:	50.2 KB 
ID:	36470

    The linked table form lists all the tables to be linked from each external app - in this case a total of 305 linked tables

    Click image for larger version. 

Name:	TableLinks.PNG 
Views:	19 
Size:	64.1 KB 
ID:	36469

    Before distributing a new or updated version to clients via my website, the table links are deliberately broken.
    When the client sysadmin installs the new FE, opens direct to the relink form.
    The app pulls information stored in the Access backup or config file to detect the correct table links to be used
    The relink process does a number of actions including
    1. Relink all required tables which takes 30 seconds or so for this very large app
    2. Makes any required changes to the structure or data in linked SQL & Access tables
    3. Resets all options as used by that particular client
    4. Updates the version information in the registry
    5. Updates values in the Access config file
    6. Resets folder & file paths from client settings for all linked images/docs/videos used in the bespoke app help system
    7. Various other housekeeping tasks
    8. Populates a version update log file etc ....

    Click image for larger version. 

Name:	VersionUpdateLog.PNG 
Views:	19 
Size:	15.5 KB 
ID:	36471


    The 'heavy lifting' for the relink process is done by the GetConnectionString function which covers a wide range of possible connection types - both SQL & Access

    Code:
    '############################################
    ' GetConnectionString 04/08/2010
    ' Used to create connection strings for each of the link types/tables in use
    '############################################
    Function GetConnectionString(strLinkType, _
                                strLinkServer, _
                                strLinkDatabase, _
                                strLinkUsername, _
                                strLinkPassword) As String
            
        Select Case strLinkType 'Get the link type to determine the connection string
    #If SQL_DRIVER = "Native" Then
            Case "SQL"
                GetConnectionString = "ODBC;DRIVER={SQL Native Client};" & _
                                        "SERVER=" & Nz(strLinkServer, "") & ";" & _
                                        "DATABASE=" & Nz(strLinkDatabase, "") & ";" & _
                                        "APP=SchoolDataAnalyser;" & _
                                        "UID=" & Nz(strLinkUsername, "") & ";" & _
                                        "PWD=" & Nz(strLinkPassword, "")
            Case "SQL-Trusted"
                GetConnectionString = "ODBC;DRIVER={SQL Native Client};" & _
                                        "SERVER=" & Nz(strLinkServer, "") & ";" & _
                                        "DATABASE=" & Nz(strLinkDatabase, "") & ";" & _
                                        "APP=SchoolDataAnalyser;" & _
                                        "Trusted_Connection=yes;"
    #ElseIf SQL_DRIVER = "MDAC" Then
            Case "SQL"
                GetConnectionString = "ODBC;DRIVER={SQL Server};" & _
                                        "SERVER=" & Nz(strLinkServer, "") & ";" & _
                                        "DATABASE=" & Nz(strLinkDatabase, "") & ";" & _
                                        "APP=SchoolDataAnalyser;" & _
                                        "UID=" & Nz(strLinkUsername, "") & ";" & _
                                        "PWD=" & Nz(strLinkPassword, "")
            Case "SQL-Trusted"
                GetConnectionString = "ODBC;DRIVER={SQL Server};" & _
                                        "SERVER=" & Nz(strLinkServer, "") & ";" & _
                                        "DATABASE=" & Nz(strLinkDatabase, "") & ";" & _
                                        "APP=SchoolDataAnalyser;" & _
                                        "Trusted_Connection=yes;" & _
                                        ""
    #End If
            Case "Access"
                If Nz(strLinkPassword, "") <> "" Then
                    GetConnectionString = "MS Access;" & _
                                        "PWD=" & Nz(strLinkPassword, "") & ";" & _
                                        "DATABASE=" & Nz(strLinkServer, "") & Nz(strLinkDatabase, "")
                Else
                    GetConnectionString = "MS Access;" & _
                                        "DATABASE=" & Nz(strLinkServer, "") & Nz(strLinkDatabase, "")
                End If
            Case Else
                GetConnectionString = ""
        End Select
    
    End Function
    The string values are of course taken from the link types table - see form in first screenshot

    This is part of the relink code which loops through each of the 305 tables in turn, shows progress & adds info to the log file

    Code:
    Function RelinkAllTables(boStatusBar As Boolean, boStatusMessage As Boolean) As Boolean
    ...
     strText2 = "Getting data for new connections"
      ...
        
        Set MySet = db.OpenRecordset("qryTableLinkInfo", dbOpenSnapshot)
       
        MySet.MoveLast
        MySet.MoveFirst
        Dim intCountOflinks As Integer
        Dim intCurrentLink As Integer
        intCountOflinks = MySet.RecordCount
        icount = MySet.RecordCount
        intCurrentLink = 0
            
        SetupProgressBar 'reset to zero
        DoEvents
        
      
        Do Until MySet.EOF
            intCurrentLink = intCurrentLink + 1
            strText2 = "Relinking table " & intCurrentLink & " of " & intCountOflinks & " - " & MySet!TableAlias
            'Debug.Print strText2
            If boStatusBar Then
                StatusBar strText2
                frm.LblHelpText.Caption = strText2 & " . . ."
                UpdateProgressBar
                DoEvents
            End If
            
            If MySet!LinkActive = True Then
                strConnectionString = ""
                strConnectionString = GetConnectionString(MySet!LinkType, MySet!LinkServer, MySet!LinkDatabase, MySet!LinkUsername, MySet!LinkPassword)
             '   Debug.Print strConnectionString
                If strConnectionString = "" Then
                    strStatusMessage = strStatusMessage & MySet!TableName & " has a bad linktype, "
                    boMakeLink = False
                    RelinkAllTables = False
                End If
            
                If LinkTable(MySet!TableName, MySet!TableAlias, strConnectionString, True) = False Then 'Link the table
                    strStatusMessage = strStatusMessage & MySet!TableName & ", "
                    boMakeLink = False
                    RelinkAllTables = False
                End If
            End If
            
            DoEvents
            MySet.MoveNext
        Loop
        
      ....
    End Function
    HTH
    Any questions?
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  2. #17
    pkstormy's Avatar
    pkstormy is offline Access/SQL Server Expert
    Windows XP Access 2003
    Join Date
    Mar 2010
    Location
    Madison
    Posts
    682
    Hey Colin,

    Inregards to your original question about linking versus importing. For the confidential SQL Server data at the department of medicine,the staff wanted a quick way they could work on an exact clone of the SQL Servertables (with data up to the last minute) and they wanted to be able to refresh it on the fly as needed. They wanted live data but not live tables (my first attempt was with SSIS) but they then wanted permissions to run SSIS any time they wanted to and didn't want their updates to affect others (which would have meant a ton of different SSIS packages). The scientists wanted the data in (imported) tables in MS Access sothey could design nasty, nested queries against the data, export it to SPSS or SAS. And If they accidently updated the data, they wanted the ability to quickly push a button and get a fresh dataset to start over again. The students wanted the tables in MSAccess sothey could mail-merge Word documents to the tables (I had trouble mailmerging in Word to linked SQL Server tables - for some reason it wouldn't recognize the tables. I planned on investigating it later.)

    All of them though, wanted the ability to push abutton and update the data on the fly with live data at any time during the day/night. I searched for VBA methods to execute an SSIS package but it then dawned on my that this still wouldn't allow them to update data as they needed to for their analysis. Hence my routine to import the SQL Server data. It offers a protection that is totally separate from live data so they can manipulate the data all they want without affecting any other scientists or staff. This was something they liked!

    I'll take a closer look at the information you supplied above.


  3. #18
    pkstormy's Avatar
    pkstormy is offline Access/SQL Server Expert
    Windows XP Access 2003
    Join Date
    Mar 2010
    Location
    Madison
    Posts
    682
    I forgot to say thank you for the information! I really appreciate it Colin.

  4. #19
    pkstormy's Avatar
    pkstormy is offline Access/SQL Server Expert
    Windows XP Access 2003
    Join Date
    Mar 2010
    Location
    Madison
    Posts
    682
    For the querying and reporting aspect, I used the approach mentioned above.

    When it came to actual research applications, all users were connecting via a secured connection with vpn (patients were not allowed to connect to it and patients did not need to connect to the study). For the main application, using MS Access as a front-end to SQL Server linked tables allowed me to use the flexibility that MS Access has. Using linked SQL Server tables (again, in the main application only), made it much easier designing the research applications with all the complex logic they required. Using MSAccess, I was able to securely lock down the interface preventing users in the MS Access front-end from gaining access to the raw data by disabling the 'shift key' trick used in MSAccess to get behind the form design (ie. held down when opening MS Access). No one could get direct access to the linked data tables via the application which was key, especially since the code entailed a lot of complex randomizing code, multi-plex scheduling, daily call lists, financials and other patient confidential information. The MS Access front-end code was compiled into an *.accde file for further security and performance. I have a printout of 1 (and only 1) of the randomization routines which is over an inch thick!

    Anyway, to send out new changes, after I compiled the new *.accde, I simply copied it to the share drive over the existing file and then sent out an email for users to close/re-open the application (via the main menu of course). It was a quick, easy way for 1 guy to manage a lot of development.

    The biggest problem I had was with constant logic changes by the scientists. No matter how much I customized and setup a modular system to re-use the same components, almost every new researcher came up with about 400 changes I couldn't predict.

  5. #20
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Hi

    That (linking vs importing) makes more sense now
    For training purposes we used a backup copy of the live SQL datafile from the previous day or similar However it was still a linked SQL datafile (albeit approx. 1.5GB) .
    Because it was linked, there were never any issues with hitting a 2GB limit in Access.

    Once again, users could safely play around & screw things up without it mattering.
    Once the training session was completed, the FE was relinked to the live datafile

    We didn't need to allow 'students' (teachers in our case) to access the training datafile at any time of day or night.
    However, staff were able to do so from home at almost any time of day or night using Terminal Services.
    Many sadly did so in the middle of the night as our logs were able to confirm The only exception for remote access was whilst the SQL datafile was updated from the main school information systems database each night That update took 30-45 minutes to complete
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Many to Many, many services with many managers
    By Haz in forum Database Design
    Replies: 3
    Last Post: 11-04-2017, 04:56 PM
  2. SQL Server Reporting Services
    By RayMilhon in forum SQL Server
    Replies: 0
    Last Post: 05-13-2013, 04:16 PM
  3. Replies: 2
    Last Post: 03-30-2012, 02:37 AM
  4. Replies: 0
    Last Post: 03-29-2011, 09:37 AM
  5. Replies: 2
    Last Post: 04-16-2010, 09:24 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums