Results 1 to 10 of 10
  1. #1
    Peter M is offline Advanced Beginner
    Windows 7 64bit Access 2013 32bit
    Join Date
    Dec 2017
    Posts
    67

    Using a Procedure stored in Db1 to manipulate data, make chages, etc. in Db2

    I need to run the same sub-routine multiple times in dozens of linked databases. Currently I keep copying code and don't like that. As an example, I have a sub-routine that loops through numerous excel tables and import the data; broken up by different databases.
    This is a simplification, but essentially I have a Front End that links to 20 Site databases. All of the databases and tables following standardized naming conventions and I use code in the Front End to link to the Site databases. Each Site contains about 1Gb of a data. To update the data in each Site, sub-routines are used to load the data from Excel files that are less than 1.4 million records each. Sites are updated on an as-needed basis (i.e. not every month).
    Currently, I am copying the sub-routines into each Site database. This leads to all sorts of problems when I need to tweak something as I have to change it in multiple spots.
    e.g.
    Public Sub GetEachWeek(varSiteName as string)
    'First week
    varSourceFile=trim(varSiteName & "_Week1")
    Docmd.transferspreadsheet acImport, varDestTable, varSourceFile, etc.
    'rest of weeks
    For i=2 to 5
    {code to create TempTable, transferspreadsheet to tempTable, append to varDestTable for weeks 2, 3, 4, 5 in a month, delete TempTable}
    next I
    end sub 'End GetEachWeek
    How do I set this up in the Front End so I can use the code in Front End (written once) to covers all Sites (1 to 20)?
    e.g.
    For varSiteNumber=1 to 20
    set the activeDB to ("Site_" & varSiteNumber)
    run GetEachWeek(varSiteName)
    next varSiteNumber

    And yes, I know, with a dataset this size I shouldn't be using Access but I've got to make do with the tools I have.
    (Alternatively, is there a straightforward way to do the opposite? i.e. call a subroutine from a different database, e.g. within the Site_01.accdb module have the vba line "SiteNumberStr = [dbFrontEnd.MyFunctionsModule.func_pad2char(SiteNum berInt)]"?)
    Thank you in advance for any guidance, I keep thinking there has to be a straightforward way but I'm not finding it.

  2. #2
    Gicu's Avatar
    Gicu is offline VIP
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi Peter,
    Not quite sure I get what you're saying. At one point you mention that you link your front-end to the "site databases". I assume those are Access "back-end" files, having only local tables, and the table names and structure are the same across your all 20 sites. Why the need to run the code from the back-end? You should have a table in your front-end with the location of all 20 sites (full file name, maybe an optional yes/no field to help you select sites that do need updating) and another table listing the tables that need to be linked. Then you just loop through your sites table (filtered to show only the ones needing updates), get the site full path and name, establish another recordset based on your tables table, loop through that and link them in, run your subs to update them, delete all linked tables, move to the next site and repeat.

    Is there something that I missed from your original post that makes you need to run the code in the site dbs instead of front-end?

    Cheers,
    Vlad

  3. #3
    Peter M is offline Advanced Beginner
    Windows 7 64bit Access 2013 32bit
    Join Date
    Dec 2017
    Posts
    67
    Thanks Vlad,

    You are correct that the Front End links to the multiple Back End Site databases. The part I wasn't clear on is the importing data into the back-end databases uses code and the data refreshes are frequent. I blow through the 2 Gb limit if I try to use the Front End to import the data and push it out to the Site Dbs; even though after a compress & refresh the Front End dbs size is negligible. I have too many files to load1, compress, load2, compress, etc.

    At the moment, for each new Site (e.g. NewSite), I copy a database shell with a Module1 (or copy a previous site and purge the data) that has the data import sub-routines. I then open up NewSite, go to the Immediate Window, and run the procedures to import the data. This data import loads from multiple Excel tables, creates 4 new Tables, appends the data, then applies indexes.
    I have found that as I go I have tweaked my code in places (e.g. error correction routines, using XML to import from CSV in one case instead of Excel, adding a new field to the index). For example, a field that was being used as a primary key failed due to record duplication. This is a data error and needs to be resolved but rather than completely fail I put an error check in (for that one site) that sets it as a non-unique index and posts an error message to the user letting me know there was a problem with this dataset that needs closer review. These Site dbs are all in the order of 2 million records and 800 - 1100 MB in size.

    However, I also go back to other sites and refresh data or re-index fields. In some cases I am doing a complete data replacement. In doing this, I have ended up with numerous copies of the same code in "Module1" - but all slightly different.

    If possible, I would like to store the code in one module and only reference that code, rather than copying it multiple times and trying to remember where the latest-and-greatest version of the code is.

    Peter
    Last edited by Peter M; 04-10-2018 at 11:41 AM. Reason: Further Clarification

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    One database could be a code library and nothing else. Read up on variable scope (here's one)
    https://support.microsoft.com/en-us/...r-applications

    while the text implies that it's relevant to a workbook, I think that's an oversight, especially since the page title supports universality among vba apps that support public modules. The PUBLIC SCOPE portion is basically saying, you can refer to modules the same way as any other library that you're used to referencing, provided you correctly design those modules in the library db. Watch out for duplicity in naming things. If you don't like that link, you can probably find something you like better on the subject.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    Gicu's Avatar
    Gicu is offline VIP
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi Peter,

    Have you tried to automate those site back-ends from within your front-end? I am attaching some code from one of my utilities (http://forestbyte.com/ms-access-utilities/fba-fuze/):

    Code:
    Option Compare Database
    Option Explicit
    Dim appAccess As Access.Application, boLoadOK As Boolean
    Private Function vcMakeAllInOne() As Boolean
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef, qdf As DAO.QueryDef
    Dim iCount As Integer, strFE_DB As String
    Dim sFE_Name As String, sA1 As String, sA1FullName As String
    Dim strMT_SQL As String, sTable As String, sTable_Local As String
    Dim rstLetters As DAO.Recordset, strLetterName As String
    Dim strAPP_SQL As String
    'variable needed for indexes
    Dim fld As DAO.Field, ind As DAO.Index, indPK As DAO.Index
    Dim strIndexes As String, strIndex() As String, strPrimary As String, iIndex As Integer, tdfLocal As DAO.TableDef
    Dim iOrdinalPosition As Integer, dbFuze As DAO.Database, strNonUniqueIndexes As String, strNonUniqueIndex() As String
    Dim sA1Folder As String
    
    
    On Error Resume Next
    
    
    vcMakeAllInOne = False
    
    
    strFE_DB = Nz(Me.FrontEndTemplate, "")
    If strFE_DB = "" Then
        MsgBox "Please select the database and try again!", vbCritical, "No Access file..."
        Exit Function
    End If
    
    
    If (Right(Trim(strFE_DB), 3) <> "mdb" And Right(Trim(strFE_DB), 5) <> "accdb") Then
        MsgBox "Please select a Microsoft Access database - mdb or accdb- and try again!", vbCritical, "No mdb or accdb file..."
        Exit Function
    End If
    
    
    If Len(Dir(Trim(strFE_DB), 3)) = 0 Then
        MsgBox "The file you selected does not exist!", vbCritical, "No mdb or accdb file..."
        Exit Function
    End If
    
    
    sA1FullName = Nz(Me.AllInOneFullName, "")
    sA1Folder = Left(sA1FullName, InStrRev(sA1FullName, "\"))
    
    
    
    
    DoCmd.Hourglass True
    Me.lblWorking.Visible = True
    Me.Repaint
    
    
    
    
    
    
    If Dir(sA1Folder, vbDirectory) = "" Then MyMkDir (sA1Folder)
    
    
    'sFE_Name = Mid(strFE_DB, InStrRev(strFE_DB, "\") + 1)
    
    
    If IsNull(Me.AllInOneFullName) Then
        If Right(Trim(strFE_DB), 3) = "mdb" Then
            sA1 = Left(strFE_DB, Len(strFE_DB) - 4) & "_A1.mdb"
        Else
            sA1 = Left(strFE_DB, Len(strFE_DB) - 6) & "_A1.accdb"
        End If
    
    
        Me.AllInOneFullName = sA1
    Else
        sA1 = sA1FullName
    End If
    
    
    
    
    Me.Dirty = False
    
    
    Call CopyFile(strFE_DB, sA1)
    
    
    Set dbFuze = CurrentDb
    'lets export the hlpTables table to the all-in-one db
    DoCmd.TransferDatabase acExport, "Microsoft Access", sA1, acTable, "hlpTables", "usysFUZE_Tables", True
    'we need to replace the linked tables with local tables
    Dim dbA1 As DAO.Database
    
    
    Set appAccess = New Access.Application
    appAccess.Visible = False
    appAccess.OpenCurrentDatabase sA1
    'repaint the form
    Me.Requery
    Me.Repaint
    Me.lblWorking.Visible = True
    appAccess.Echo False
    appAccess.DoCmd.SetWarnings False
    
    
    Dim intAutoCompact As Integer
    intAutoCompact = appAccess.GetOption("Auto Compact")
    'set compact on close
    If Me.CompactOnClose = True Then appAccess.SetOption ("Auto Compact"), 1
    appAccess.Visible = False
    Me.Requery
    Me.Repaint
    
    
    'vcJan 26, 2010
    Dim sLocalTable As String, sSourceTable As String, sDatabaseType As String, sConnect As String, sType As String, rstTables As DAO.Recordset
    
    
    Set rstTables = appAccess.CurrentDb.OpenRecordset("usysFUZE_Tables", dbOpenDynaset)
    
    
    
    
    DoCmd.SetWarnings False
    
    
    
    
    For Each tdf In appAccess.CurrentDb.TableDefs
        sLocalTable = tdf.Name
        sSourceTable = tdf.SourceTableName
        sConnect = tdf.Connect
        If sConnect = "" Then
            sType = "Local"
        ElseIf InStr(sConnect, "Access") > 0 Or InStr(sConnect, "mdb") Or InStr(sConnect, "accdb") > 0 Then
            sType = "Access"
        ElseIf InStr(sConnect, "ODBC") > 0 Then
            sType = "ODBC"
        Else
            sType = "Unknown"
        End If
        
    'write to hlpTables for de-fuzing
    rstTables.AddNew
        rstTables("LocalTable") = sLocalTable
        rstTables("SourceTable") = sSourceTable
        rstTables("ConnectionString") = sConnect
        rstTables("TableType") = sType
    rstTables.Update
    
    
        'only do linked tables
        If Len(tdf.Connect) > 0 Then
            sTable = tdf.Name
            sTable_Local = sTable & "_FBA_LOCAL"
       
            strMT_SQL = "SELECT [" & sTable & "].* INTO [" & sTable_Local & "] FROM [" & sTable & "] Where True=False;" 'vc 15Oct2009
            strAPP_SQL = "INSERT INTO  [" & sTable_Local & "] SELECT [" & sTable & "].* FROM [" & sTable & "];" 'vc 15Oct2009
                         
               'get indexes
                strIndexes = ""
                strPrimary = ""
                strNonUniqueIndexes = ""
                
                For Each ind In tdf.Indexes
                    If ind.Unique = True Then
                        strIndexes = strIndexes & "|" & ind.Name
                        If ind.Primary = True Then
                            strPrimary = strPrimary & "|" & ind.Fields
                        End If
                    Else
                         strNonUniqueIndexes = strNonUniqueIndexes & "|" & ind.Name
                    End If
                Next ind
            'run the make-table query
            appAccess.DoCmd.RunSQL strMT_SQL
                   
            
            Set dbA1 = appAccess.CurrentDb
            Set tdfLocal = dbA1.TableDefs(sTable_Local)
            On Error Resume Next
            
            'set indexes
            strPrimary = Replace(Replace(strPrimary, "|", ""), "+", "")
            If strPrimary = "" Then GoTo vcSetIndexes
            Dim sComposite() As String, iCountKey As Integer
            sComposite() = Split(strPrimary, ";")
            
            'drop column and add as autonumber
            If InStr(tdf.Connect, "MySQL") > 1 Then         'MySQL
                If tdfLocal.Fields(strPrimary).Type = dbLong Then 'if it is long integer assume is autonumber
                'store ordinal position
                    iOrdinalPosition = tdfLocal.Fields(strPrimary).OrdinalPosition
                    appAccess.DoCmd.RunSQL ("ALTER TABLE [" & tdfLocal.Name & "] DROP COLUMN [" & strPrimary & "];")
                    Call fCreateAutoNumberField(sTable_Local, strPrimary, dbA1, iOrdinalPosition)
                End If
                tdfLocal.Fields.Refresh
            ElseIf InStr(tdf.Connect, "Oracle") > 1 Then     'Oracle 'vc20140331
                If tdfLocal.Fields(strPrimary).Type = dbDecimal Then 'if it is decimal assume is autonumber
                'store ordinal position
                    iOrdinalPosition = tdfLocal.Fields(strPrimary).OrdinalPosition
                    appAccess.DoCmd.RunSQL ("ALTER TABLE [" & tdfLocal.Name & "] DROP COLUMN [" & strPrimary & "];")
                    Call fCreateAutoNumberField(sTable_Local, strPrimary, dbA1, iOrdinalPosition)
                End If
                tdfLocal.Fields.Refresh
            
            End If
            
            'set primary key
            Set ind = tdfLocal.CreateIndex("PrimaryKey")
            With ind
            
                For iCountKey = 0 To UBound(sComposite)
                    .Fields.Append .CreateField(sComposite(iCountKey))
                    .Unique = True
                    .Primary = True
                Next
                
            End With
            tdfLocal.Indexes.Append ind
            tdfLocal.Indexes.Refresh
                   
    vcSetIndexes:
            
     'now the rest of the indexes
     'first the unique ones
            strIndex = Split(strIndexes, "|")
            For iIndex = 0 To UBound(strIndex)
                If strIndex(iIndex) <> "" Then
                    Set ind = tdfLocal.CreateIndex(strIndex(iIndex))
                    With ind
                        .Fields.Append .CreateField(strIndex(iIndex))
                        .Unique = True
                    End With
                    tdfLocal.Indexes.Append ind
                    tdfLocal.Indexes.Refresh
                End If
            Next
     'now the non-unique
            strNonUniqueIndex = Split(strNonUniqueIndexes, "|")
            For iIndex = 0 To UBound(strNonUniqueIndex)
                If strNonUniqueIndex(iIndex) <> "" Then
                    Set ind = tdfLocal.CreateIndex(strNonUniqueIndex(iIndex))
                    With ind
                        .Fields.Append .CreateField(strNonUniqueIndex(iIndex))
                        .Unique = False
                    End With
                    tdfLocal.Indexes.Append ind
                    tdfLocal.Indexes.Refresh
                End If
            Next
    
    
            'lets add the data
            appAccess.DoCmd.RunSQL strAPP_SQL 'vc 15Oct2009
            
            appAccess.DoCmd.DeleteObject acTable, sTable
        End If
    Next tdf
    
    
    Set dbA1 = appAccess.CurrentDb
    For Each tdf In dbA1.TableDefs
        If Right(tdf.Name, 10) = "_FBA_LOCAL" Then
            tdf.Name = Left(tdf.Name, Len(tdf.Name) - 10)
        End If
    Next tdf
    
    
    SetApplicationTitle (appAccess.CurrentDb.Properties("AppTitle") & " <<ALL-IN-ONE>>")
    appAccess.RefreshTitleBar
    appAccess.DoCmd.SetWarnings True
    appAccess.Echo True
    
    
    If Me.LeaveFileOpen = False Then
        appAccess.Quit acQuitSaveAll
    Else
        appAccess.Visible = True
    End If
    
    
    
    
    
    
    On Error Resume Next
    'lets copy additional files
    vcCopyAdditionalFiles
    
    
    
    
    
    
    
    
    EXIT_vcMakeAllInOne:
    DoCmd.Hourglass False
    vcMakeAllInOne = True
    Application.Quit
    End Function
    You can see in the example above how I open a new instance of Access, open a new file (in your case that would be one of your sites) and process it to my needs.

    Cheers,
    Vlad

  6. #6
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    @Gicu
    I tried to go to your site, but I got a 404 error. Turns out there is a "):" included in your posted link (Post #5). In the web page I edited the address to remove the "):", then hit enter and TaDa!! there it is.

    Now the bad news. I tried to look at some VBA Code Samples and tried to download Fuze but again got the 404 error. Doesn't appear that the links are pointed to the correct location....

  7. #7
    Gicu's Avatar
    Gicu is offline VIP
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi Steve,

    Just sent an email to the web host, all my files are gone.... Thank you very much for letting me know!

    Cheers,
    Vlad

  8. #8
    Peter M is offline Advanced Beginner
    Windows 7 64bit Access 2013 32bit
    Join Date
    Dec 2017
    Posts
    67
    Hi Vlad,

    Thank you for the code dump. I will have to look at it carefully but I think it will explain how to accomplish my needs. If I understand correctly (at high level), you are opening a new instance of Access and then explicitly referencing it e.g. appAccess.DoCmd.RunSQL etc.

    Thank you, if it all works I will tag as solved.

    Peter

  9. #9
    Gicu's Avatar
    Gicu is offline VIP
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Yes Peter, you got it.

    Cheers,
    Vlad

  10. #10
    Gicu's Avatar
    Gicu is offline VIP
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi everyone,

    Just to let you know that all the files on my site (forestbyte.com) are now back.

    Cheers,
    Vlad

Please reply to this thread with any new information or opinions.

Similar Threads

  1. write stored procedure with 'if else'
    By shital in forum Access
    Replies: 9
    Last Post: 04-11-2018, 11:47 AM
  2. Passing Params To Stored Procedure
    By jo15765 in forum Programming
    Replies: 3
    Last Post: 04-11-2017, 07:19 AM
  3. Help with SQL stored procedure
    By emmahope206 in forum Access
    Replies: 1
    Last Post: 05-24-2016, 05:03 PM
  4. Replies: 33
    Last Post: 09-25-2015, 08:39 AM
  5. Replies: 5
    Last Post: 09-26-2011, 03:58 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