Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Access Frontend Database Creating "Local" Tables Using Visual Basic

Posted on 2014-01-31
Medium Priority
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
Question by:spaced45
  • 3
  • 3
  • 2
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.
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

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.
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.


Author Comment

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?

Author Comment

ID: 39825698

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.
LVL 21
ID: 39825759

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.
LVL 38

Accepted Solution

Jim P. earned 2000 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"

    "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   " & _

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.

Author Closing Comment

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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This is an article on how to answer questions, earn points and become an expert.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

578 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