Solved

Access Frontend Database Creating "Local" Tables Using Visual Basic

Posted on 2014-01-31
8
1,122 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
  • 3
  • 3
  • 2
8 Comments
 
LVL 21
Comment Utility
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.
Comment Utility
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
Comment Utility
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
 
LVL 1

Author Comment

by:spaced45
Comment Utility
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 1

Author Comment

by:spaced45
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Looks like it working perfectly. thank you for all the assistance
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
Outlook Free & Paid Tools
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

762 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now