Solved

Access Frontend Database Creating "Local" Tables Using Visual Basic

Posted on 2014-01-31
8
1,196 Views
Last Modified: 2014-02-01
I posted a question yesterday requesting assistance on optimizing split database for Access due to network performance. Here is the link to my initial question I got good advice which led me to post this question.

I looking to have my front end DB create "local" tables based on linked tables to improve performance. I was thinking that I would only do this for reference tables and leave the transactional tables still as linked tables. All my tables on the backend have a last mod timestamp in them. I was thinking to create a table containing the table name and max mod timestamp. This table would then link to the frontend so on open it would be able to check if the local tables need to be updated.  I have a form on the frontend that opens on db open that the user logs in with. I was hoping to attach some VB to that form that would run the check on the last mod table table and then execute code to update the tables as needed.

Is this possible? Has anyone done anything similar? Any assistance with this would be very much appreciated as my deadline to get this done is fast approaching

Thank you
0
Comment
Question by:spaced45
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
8 Comments
 
LVL 21
ID: 39824843
I use a local back end for this all created with VBA code/

Also see: Lookup Table Manager
This utility started because of a project I was working on, that had several large lookup tables, that were semi-static, that means that they are not changed very often, but they can actually be updated when the user wish to add something. Until this I was using almost always linked tables, but this time the lookup tables were really large and the network as a bit slow. So I started thinking in a way to make this lookup tables local, this would increase performance and reduce network traffic.
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 39824865
Here is code to build a table on the fly:
Public Function ExportCensusData(FacCode As String)

Dim SQL As String
Dim DB As DAO.Database
Dim RS As DAO.Recordset

Dim TableName As DAO.TableDef
Dim FieldName As DAO.Field
Dim FieldProperty As DAO.Property

Dim Qry As DAO.QueryDef


Dim I As Integer
Dim ResID As Integer
Dim ActionCd As String

'Dim FacCode As String

'FacCode = "Census"



If DoesTblExist("LocCensusTbl") = True Then
    DoCmd.SetWarnings False
    DoCmd.DeleteObject acTable, "LocCensusTbl"
    DoCmd.SetWarnings True
End If

Set DB = CurrentDb()
Set TableName = DB.CreateTableDef("LocCensusTbl")
    
    With TableName
        .Fields.Append .CreateField("Facility_Code", dbText, 5)
        .Fields.Append .CreateField("Client_Id_Number", dbText, 35)
        .Fields.Append .CreateField("Effective_Date", dbDate)
        .Fields.Append .CreateField("Status_code", dbText, 5)
        .Fields.Append .CreateField("Action_code", dbText, 5)
        .Fields.Append .CreateField("Adt_tofrom", dbInteger)
        .Fields.Append .CreateField("Primary_payer_code", dbText, 20)
        .Fields.Append .CreateField("Rugs_code", dbText, 5)
        .Fields.Append .CreateField("Rugs_modifier_code", dbText, 2)
        .Fields.Append .CreateField("Adt_tofrom_loc", dbText, 100)
        .Fields.Append .CreateField("Assess_ref_date", dbDate)
        .Fields.Append .CreateField("Outpatient_status", dbText, 1)
        .Fields.Append .CreateField("Admission_type_code", dbInteger)
        .Fields.Append .CreateField("Admission_source_code", dbInteger)
        .Fields.Append .CreateField("UnitDescription", dbText, 35)
        .Fields.Append .CreateField("FloorDescription", dbText, 50)
        .Fields.Append .CreateField("RoomDescription", dbText, 60)
        .Fields.Append .CreateField("BedDescription", dbText, 30)
        .Fields.Append .CreateField("HospitalStayFrom", dbDate)
        .Fields.Append .CreateField("HospitalStayTo", dbDate)
        .Fields.Append .CreateField("RESIDENT_ID", dbLong)
        .Fields.Append .CreateField("RES_STAY_ID", dbLong)
        .Fields.Append .CreateField("SeqNum", dbInteger)
        .Fields.Append .CreateField("IdNum", dbLong)
    End With
    
    With TableName
        .Fields("Facility_Code").DefaultValue = FacCode
        .Fields("IdNum").Attributes = dbAutoIncrField
        .Fields("UnitDescription").AllowZeroLength = True
        .Fields("FloorDescription").AllowZeroLength = True
        .Fields("RoomDescription").AllowZeroLength = True
        .Fields("BedDescription").AllowZeroLength = True
    End With

DB.TableDefs.Append TableName

Set FieldName = TableName.Fields("Effective_date")
Set FieldProperty = FieldName.CreateProperty("Format", dbText, "mm/dd/yyyy")
FieldName.Properties.Append FieldProperty

Set FieldName = TableName.Fields("Assess_ref_date")
Set FieldProperty = FieldName.CreateProperty("Format", dbText, "mm/dd/yyyy")
FieldName.Properties.Append FieldProperty

End Function

Open in new window


The DoesTblExist function:
Public Function DoesTblExist(strTblName As String) As Boolean
    On Error Resume Next
    Dim DB As DAO.Database
    Dim Tbl As DAO.TableDef
    Set DB = CurrentDb
    Set Tbl = DB.TableDefs(strTblName)
    If Err.Number = 3265 Then   ' Item not found.
       DoesTblExist = False
       Exit Function
    End If
    DoesTblExist = True
End Function

Open in new window

0
 
LVL 21
ID: 39824881
I forgot to post this link:

The TempTables.MDB illustrates how to use a temporary MDB in your Microsoft Access app.

I use a local back end for this so it can easily be compacted. I can also update the front end without having to refresh the local cached look up tables.
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 1

Author Comment

by:spaced45
ID: 39825690
I think that this will certainly be able to do the trick though from what I am seeing in the code this would be executed once if the table doesn't exist. Right? What if I make a change to the table and need it to execute this again so that the local table/s are updated?

Before I was able to read the posts I took it a step further and pre-created the tables with no data. I was then that when the database opened it could, first check if the local table contained data and second if data was present then check for updates.

Would this still be possible?
0
 
LVL 1

Author Comment

by:spaced45
ID: 39825698
HiTech,

tried to take a look at how the Look Manager example worked but I got some errors dealing with conversions and user-level permissions that are no longer supported in later versions of access.
0
 
LVL 21
ID: 39825759
spaced45,

Did you rad the article text about the Lookup Table Manager. It has some incites into what you will need need to address. to make this work.

For me, since 100 Mb NICs and Citrix/Terminal Server were available I have not had to deal with any network performance issues. Even with WAN/VPN and wireless connections.  The only performance issues I deal with now are programming/database design issues.

I did a cursory scan of your precious post. Is part of your network peformance issues related to using a WAN via a VPN?   In these situations I use a Terminal Server.  This solves performance issues with a WAN/VPN and wifi networks.. I have some sites with 30+ concurrent  users using Terminal Services from multiple cities across the US with no performance issues or database corruption.
0
 
LVL 38

Accepted Solution

by:
Jim P. earned 500 total points
ID: 39825769
I think that this will certainly be able to do the trick though from what I am seeing in the code this would be executed once if the table doesn't exist. Right?

It depends on how you wrap the DoesTblExist If statement. The way this is coded is will delete the table and replace it each time it is run.

Changing it to something like this:
If DoesTblExist("LocCensusTbl") = False Then
     Set DB = CurrentDb()
    Set TableName = DB.CreateTableDef("LocCensusTbl")
    
    With TableName
     ....
End If

Open in new window

will do a one time create.

Note that I haven't found a way to create lookups/combo boxes and similar stuff from code yet. For those I have a MyTable_Base that is an empty table.

Then I do it as something like:
DoCmd.SetWarnings False
If DoesTblExist("Religion_XRef") = True Then
    DoCmd.DeleteObject acTable, "Religion_XRef"
End If
DoCmd.CopyObject , "Religion_XRef", acTable, "Religion_XRef_Base"

SQL = "INSERT INTO Religion_XRef(RELIGION_ID, RELIGION_CODE, DESCRIPTION, DATE_TIME_INSERTED_UPDATED, ENTITY_ID, Client_Code_Description) " & _
    "SELECT RELIGION_ID, RELIGION_CODE, DESCRIPTION , '3/2/2001 4:12:45 PM' AS DATE_TIME_INSERTED_UPDATED, 1 AS ENTITY_ID, 'Unk' as Client_Code_Description   " & _
    "FROM RELIGION "

DoCmd.RunSQL SQL, False

DoCmd.SetWarnings True

Open in new window


The Client_Code_Description  is a lookup field from another table. I want the users to pick from the pre-built list in another table. So that is how I'm doing it. Either way is a valid option.

Programming hint 1: You want to leave the DoCmd.SetWarnings False in set as little as possible. You will commonly see the code below in my programming:
SQL = "INSERT INTO TableNm( Field1, Field2, Field3, Field4) " & _
    "SELECT Field1, Field2, Field3, Field4 " & _
    "FROM TableNm2" & _
    "WHERE IsDate(Field1) = False "
    
DoCmd.SetWarnings False
DoCmd.RunSQL SQL, False
DoCmd.SetWarnings True

Open in new window


Programming hint 2: You may want to create your front-end DB with a name like Vacation_Sked_V001.mdb or Vacation_Sked_V001.accdb. That way you can build a routine in the startup that checks if there is Vacation_Sked_V*.mdb on \\ServerNm\ShareNm and automatically copies it or asks the user if they want the new version. Basically it is an automated routine that will allow you to develop as needed and put the updates out without trying to build an install routine beyond the first one.
0
 
LVL 1

Author Closing Comment

by:spaced45
ID: 39826691
Looks like it working perfectly. thank you for all the assistance
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Outlook for dependable use in a very small business   This article is about using the Outlook application (part of Microsoft Office) in a very small business, or for homeowners where dependability and reliability are critical requirements. This …
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question