Area/Factory Grid (1 Viewer)

MickJav

AWF VIP
Local time
Today, 07:24
Joined
Nov 28, 2005
Messages
1,992
I'm Just starting work on a section of a project and need a bit of network advice

Local table version
I'm going to build a grid form with a 30 fields and 30 rows (There may be a few more) grid this will be coloured Depending on the object(s)
The Local table will be filled from a data table in BE as below
AreaID PK
CtrID PK
RowID PK
LocationID
ObjectID
MachineID
EmployeeID
The downside to the local table version is Blot and coding will be more complex but not a problem.

If I create a Datafile In BE table It would use 30 rows (Depending on final grid size) and 62 Fields for each areaID
ID PK, Auto
AreaID FK
D1-D30 Would contain an Array of Elements like C123456,L:1,O:0,M:25,E:0 See list above data table

It would be simpler to code but my question is which would be best for a network

Thanks mick
 
Last edited:

moke123

AWF VIP
Local time
Today, 02:24
Joined
Jan 11, 2013
Messages
1,492
The downside to the local table version is Blot and coding will be more complex but not a problem.
I often use a temporary database rather than just a temp table. I create a new empty temp DB on startup and kill it on close. I use template tables to create the table structure in the temp DB on demand. The template tables make it easier to incorporate changes. I have an example if you need it (based on code stolen from DBG)
 

MickJav

AWF VIP
Local time
Today, 07:24
Joined
Nov 28, 2005
Messages
1,992
Thanks @moke123 and belated thanks to @theDBguy that would solve my Issues I could use my BE table and the temp Db Blot Problem solved.

mick
 

MickJav

AWF VIP
Local time
Today, 07:24
Joined
Nov 28, 2005
Messages
1,992
Just Had a thought If I create a template Db I could copy it when my splash screen opens then link to the tables I could use it for all my tmp tables as I have a number of them Then just kill it before copying the template??
 

MickJav

AWF VIP
Local time
Today, 07:24
Joined
Nov 28, 2005
Messages
1,992
Got The Copy Working And will post an example on my site later today thanks to @moke123 & @theDBguy your example helped A LOT.

mick
 

MickJav

AWF VIP
Local time
Today, 07:24
Joined
Nov 28, 2005
Messages
1,992
Update I had a minor issue with the code in example In that the Nav Pan kept displaying so went to Tabledef object wich worked a charm

Note I delete all local linked tables first then relink them, I would note also it was a lot quicker and smother than using TransferDatabase

Code:
Function LinkLocalTables(strPath As String)
Dim m_Db As Database                        'This Database
Dim M_Links As DAO.Recordset                'Holds The Programs Linked Tables Names
Dim Tdf As DAO.TableDef
On Error GoTo HandleErr

    Set m_Db = CurrentDb()
    Set M_Links = m_Db.OpenRecordset("StblLinkedLocalTables", dbOpenTable)
    'Make Shure The Data File Exists First
    Do While Not M_Links.EOF
    m_Db.TableDefs.Delete M_Links("txtTable")
    M_Links.MoveNext
    Loop
    If adhFileExists(strPath) Then
        M_Links.MoveFirst    'Make Shure Of First Record
        'MsgBox StrPath & vbCrLf & "Tables: " & M_Links.RecordCount
        Do While Not M_Links.EOF
            Set Tdf = m_Db.CreateTableDef(M_Links("txtTable"))
                Tdf.Connect = ";DATABASE=" & strPath
                Tdf.SourceTableName = M_Links("txtTable")
            m_Db.TableDefs.Append Tdf
    
       ' DoCmd.TransferDatabase acLink, "Microsoft Access", strPath, acTable, M_Links("txtTable"), M_Links("txtTable")
        M_Links.MoveNext
        Loop
    Else
        MsgBox "Cannot Find " & strPath & vbCrLf & "Please Open Preferences And Select Your Data Files Location", vbCritical, "File Not Found"
        Exit Function
    End If

HandleExit:
    M_Links.Close
    Exit Function
    
HandleErr:
    If Err.Number = 91 Then
        Exit Function
    End If
    MsgBox Err.Number & Err.Description
    Resume HandleExit
End Function
Please note I still af a few mods to make to the function as was a copy of old link manager.
 

moke123

AWF VIP
Local time
Today, 02:24
Joined
Jan 11, 2013
Messages
1,492
Sorry Mick I should have mentioned some of the quirks. That was just some of the basics in the example. You can add a simple bit of code to hide the nav pane. I believe it appears due to adding the table.
Code:
Public Sub sHideNavPane()

    Call DoCmd.NavigateTo("acNavigationCategoryObjectType")

    Call DoCmd.RunCommand(acCmdWindowHide)

End Sub
you can add a call to this in the Public Sub CreateTable

I think you also need to drop the link to the temp tables before destroying the temp database and closing the main database or you will get a prompt for the missing table next time you open it.
 

MickJav

AWF VIP
Local time
Today, 07:24
Joined
Nov 28, 2005
Messages
1,992
No Problem My code working fine I may edit it for dropping the links first but so far had no problems.

As a thought these are all local tables that ony get accessed when spicific objects are opened maybe thats why I'm getting away with it.

I will create an example and come back and let you know when up.

mick
 

moke123

AWF VIP
Local time
Today, 02:24
Joined
Jan 11, 2013
Messages
1,492
Mick,
I'd be curious to see what the difference is between my method and yours. In mine I only add the temp tables to the temp DB when they are needed whereas I see you are adding a few at once at load time. When I first messed with the temp Db code I ran tests and found that the bloat in front end was very minimal over time. When you link and unlink tables there is a slight increase in the MySysNameMap table (in the kb's,very small) I'd be curious what effect adding several tables each time has. When I get a chance I may test it and I'll let you know.
 

MickJav

AWF VIP
Local time
Today, 07:24
Joined
Nov 28, 2005
Messages
1,992
Cool Thanks I was just going to refresh the links to begin with, If your tests show a issue I could go back to refeshing the links.

mick
 

MickJav

AWF VIP
Local time
Today, 07:24
Joined
Nov 28, 2005
Messages
1,992
Well after all the fun with linking Local tables I didn't use a local table but I am still glade we worked out what we did as will reduce blot.

Got the grid working still have a lot of work with the UI Options but working a treat and is very very quick.

2020-06-03 (1).png
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom