Solved The VBA Project cannot be read ... (1 Viewer)

[Marshall Brooks]

I got a message that my post was spam-like, so I'm going to split it up:

I maintain a database for our company and the database is about 20 years old. There are 8 users. The database is split with a back-end on the server, and a front end that each user accesses (8 copies of the front end). Some tables are read from the backend to the frontend on startup.

About a week ago, some users started getting the following error when opening the database:

The distributed database is an .accde, but I get the error with the master .accdb file as well.

<more>

 

[Marshall Brooks]

Minor update: I took the created file and copied my user forms over to it and that file works in either version of Office.

So I have everything imported and working, except for my native Macros - particularly my AutoExec macro ...

Dragging that across from the old database in Office 365 gives me the error in Office 2016.

Importing that macro: External Tools>New Data Source>Access in Office 365 gives me the error in Office 2016.

Next I'm going to try manually re-creating the macro in the new file.

 

[Marshall Brooks]

Almost there, but I want to back up a bit ...

My AutoExec macro loads a form as hidden, deletes about 8 tables from the FE if they exist, copies the same table from the backend, and then opens one of the forms.

The commands are a bit different but I could do all of this in VBA and then @bastanu 's macro would have probably fixed this.

Before, I tried to do this in VBA creating a subroutine called AutoExec() and it didn't work. The code worked, but it didn't run on startup.

Is there a way to accomplish this? I can see several options, but not sure what is best:

• I'm not sure how to have the code automatically load. Having a subroutine named AutoExec in Module1 didn't work. Would creating an AutoExec Module work?
• I could change my Access AutoExec Macro to call the AutoExec Subroutine, but I'm still using an AutoExec Macro if I do that.
• The last step of my macro opens a form - I'll call it "FORM A". I could simply call AutoExec from the OnLoad Event of Form A and then set Form A to load at startup from the This Database Options menu. Seems a bit circular, though - i.e. the first line of AutoExec loads a logout timer form. That checks for the existence of a file and then loads form downformaintenance. Then the autoexec checks for that form and exits if it is found.
• I could create a separate AutoExec Form that loads hidden and put the startup code in the Load Event for this form.

How is this normally done?

As far as the database - I loaded all of my macros EXCEPT AutoExec and it works fine in both Office 365 and Office 2016. I manually created AutoExec and loaded the hidden form, but I haven't added any other commands, but that version is working both in 365 and 2016.

 

[adhoustonj]

Almost there, but I want to back up a bit ...

My AutoExec macro loads a form as hidden, deletes about 8 tables from the FE if they exist, copies the same table from the backend, and then opens one of the forms.

The commands are a bit different but I could do all of this in VBA and then @bastanu 's macro would have probably fixed this.

Before, I tried to do this in VBA creating a subroutine called AutoExec() and it didn't work. The code worked, but it didn't run on startup.

Is there a way to accomplish this? I can see several options, but not sure what is best:

• I'm not sure how to have the code automatically load. Having a subroutine named AutoExec in Module1 didn't work. Would creating an AutoExec Module work?
• I could change my Access AutoExec Macro to call the AutoExec Subroutine, but I'm still using an AutoExec Macro if I do that.
• The last step of my macro opens a form - I'll call it "FORM A". I could simply call AutoExec from the OnLoad Event of Form A and then set Form A to load at startup from the This Database Options menu. Seems a bit circular, though - i.e. the first line of AutoExec loads a logout timer form. That checks for the existence of a file and then loads form downformaintenance. Then the autoexec checks for that form and exits if it is found.
• I could create a separate AutoExec Form that loads hidden and put the startup code in the Load Event for this form.

How is this normally done?

As far as the database - I loaded all of my macros EXCEPT AutoExec and it works fine in both Office 365 and Office 2016. I manually created AutoExec and loaded the hidden form, but I haven't added any other commands, but that version is working both in 365 and 2016.

You could put the code in the form_load() event and call that form to be the starting display form under options -> current db -> display form

 

[Marshall Brooks]

It works!!!

Thank you all!!!

I decided not to put the code in the form load event and just to convert it to a function and call that from the AutoExec macro. There might be times that I want to bypass the startup code and still open Form A and this will allow that - and if it ever gets corrupted again @bastanu 's code will fix the VBA function and worst case I just have to manually re-create one line in the AutoExec macro.

Final question (maybe ). My Startup code goes through and deletes local tables from the front end if they exist and then copies updated tables over from the backend if they don't exist - so the partial code looks like this:

    If (Not IsNull(ELookup("Name", "MSysObjects", "Name='tblHolidays'"))) Then
        DoCmd.DeleteObject acTable, "tblHolidays"
    End If
    If (IsNull(ELookup("Name", "MSysObjects", "Name='tblHolidays'"))) Then
        DoCmd.TransferDatabase acImport, "Microsoft Access", "\\network path\be.accdb", acTable, "tblHolidays", "tblHolidays", False
    End If

I can't say that is truly is related, but I've got 18 calls for ELookup on startup and that was the module that I had the most trouble importing back into the database.

I probably found that online and it works, but is there a simpler way to write this?

In English, the code would be:

If tblHolidays exists, delete tblHolidays
If tblHoldays does not exist, import tblHolidays.

 

[Josef P.]

If tblHolidays exists, delete tblHolidays
If tblHoldays does not exist, import tblHolidays.

or:
If tblHolidays exists, delete tblHolidays
import tblHolidays (without check) ... because it don't exists (deleted before)

 

[Marshall Brooks]

Is there a way to check if it exists or doesn't exist without using Elookup (or DLookup)?

@Josef P. : Good call. That SHOULD work if I trust the delete function. The problem I was running into was sometimes if it wasn't deleted, it would create tblHolidays2 (or maybe tblHolidays_Copy), but that was likely when I was stepping through code on the development build.

 

[Josef P.]

Table2DeleteSql = "select Name from MSysObjects where Name in ('tblHolidays', ...)"
with db.Openrecordset(Table2DeleteSql, ...)
    do while not .eof
        deleteTable .Fields(0).value
        .movenext
    loop
    .close
end with

private sub deleteTable(byval tabName as String)
     ' run a code you can trust :)
     ' drop table ...
end sub

BTW: This topic no longer fits the topic of the thread.

 

[Marshall Brooks]

Seems a bit complex ...

I like the simplicity of this: https://www.tek-tips.com/viewthread.cfm?qid=1771947 - I also found this: https://stackoverflow.com/questions/2985513/check-if-access-table-exists/2992743#2992743

One concern - I need to check if the table exists Locally in the front end. The same table WILL exist in the back end and I don't want to do anything that would delete it there ...

 

[Josef P.]

Suppose you want to delete 5 specific tables:

if TabelDefExists("Table1") then
     deleteTable "Table1"
end if
if TabelDefExists("TableAB") then
     deleteTable "TableAB"
end if
if TabelDefExists("TableXYZ") then
     deleteTable "TableXYZ"
end if
if TabelDefExists("Table432") then
     deleteTable "Table432"
end if
if TabelDefExists("Table5") then
     deleteTable "Table5"
end if

vs.

Table2DeleteSql = "select Name from MSysObjects where Name in ('Table1', 'TableAB', 'TableXYZ', 'Table432', 'Table5') and Type = ..."
with currentdb.Openrecordset(Table2DeleteSql, ...)
    do while not .eof
        deleteTable .Fields(0).value
        .movenext
    loop
    .close
end with

The select part could also be encapsulated to make the code more readable

dim vTabName as Variant
For each vTabName in SearchTableMatches(Names2Find:=Array("Table1", "TableAB", "TableXYZ", "Table432", "Table5"))
                                              'todo: find a better name for SearchTableMatches ;)
      deleteTable vTabName
next

 

[bastanu]

Just got back from skying and saw that you solved the initial problem - hurray!
Any particular reason why you would delete the objects (tables) and not simply refresh the data in them using a delete\append query combination? I can see the need for what your doing if the table structures change, but for a data refresh I think is overkill.

Cheers,

 

[Marshall Brooks]

@Josef P. - Point taken - I'm VERY unfamiliar with SQL ...

Two (well 4) questions:

Table2DeleteSql = "select Name from MSysObjects where Name in ('Table1', 'TableAB', 'TableXYZ', 'Table432', 'Table5') and Type = ..."
with db.Openrecordset(Table2DeleteSql, ...)

In reply 47, you don't have Type = ... In reply 49 you do. Is that correct?
Is type literally supposed to be "..." ?
Is line 2 literally supposed to have "..." ?
I might be misinterpreting the do loop, but this seems like it is going to go through every field of each selected table and delete it until no more fields exist, which seems slow ...?

@bastanu - Errrm - mainly b/c I'm about or less familiar with delete/append queries than I am with SQL. I used one once to copy data from one column to a new one and it worked the SECOND time (after I destroyed the test file copy and copied the master over again ...)

 

[Josef P.]

Theoretically, a form with the name tblHolidays could also exist in MsysObjects. => additional filter type = 1 to check only tables.

Let me write some more complete code for testing:

const Table2DeleteSql  as String = "select Name from MSysObjects where Name in ('Table1', 'TableAB', 'TableXYZ', 'Table432', 'Table5') and Type = 1"
with currentdb.OpenRecordset(Table2DeleteSql, dbopenforwardonly)
    do while not .eof ' loop over filtered records
          debug.print .fields(0) ' <-- replace later with DeleteTable procedure
          .movenext
    loop
end with

 

[Minty]

I have a local table of all tables that I maintain in a number of databases, and included in that is a field that includes a flag to "MakeLocalCopy".
You could use something similar to maintain your local copies.

As others have said, you would possibly be better placed to simply delete the data and replace rather than continually deleting the actual tables.

 

[Marshall Brooks]

@Josef P: - Thanks for looking into this. I'm assuming Reply #52 is just an example and not the new tested code, correct?
@bastanu and @Minty - I'm not opposed to just deleting and replacing the data.

For further reference - there are 9 total imported tables and they are fairly short - i.e. the largest one is something like 73 records and 5 fields.

As @bastanu inferred, the structure rarely changes and if it does, it typically either adds a new field which is not used in this FE and wouldn't affect the query (I assume), or else, the front-end needs to be revised to pick up the structure change, at which time the query could be changed also.

They do have to be transferred over a network, which slows things down, but I'm assuming that would be true for either replacing the tables or replacing the data in the tables.

Mainly, I'm not that concerned about speed or lines of code for this process - I'm trying to get away from 18 E-Lookup calls which appear to be related to the corruption issue in the first place. @Josef P. 's first comment eliminates 9 of them without any other changes to the code.

 

[Marshall Brooks]

Actually - this looks like what I need: https://www.access-programmers.co.uk/forums/threads/check-if-table-exist-delete-if-true.199209/ - Reply #4

 

[Marshall Brooks]

I marked the thread as solved, and I used the method in the #55 Link to import my tables, but I have a followup question/request for @bastanu.

There were two items that your VBA code did not address, and I was wondering if you wouldn't mind updating it:

• The Access Macros (not VBA macros, native Access) were not updated. I think one of mine was corrupt. For me, I converted mine to VBA and then just had a one-line macro to run the VBA code, so the VBA code will be repaired in the future by your module, and I might just need to recreate one line of code, but for others, it would help. I think it would be similar to how you handle forms and queries and reports, and you would just use acmacro, and I considered added this, but there were some subtleties between the various subroutines that I was unsure of.
• The code didn't handle UserForms. I know you weren't aware of these. Access doesn't mention natively importing them in the New Data Source option either, but they have a good amount of text code as part of them. In my case, I just copied them back over and they worked, but I suspect they could get corrupted and exporting them to text and importing them would be helpful. I don't know what Access calls them though, so I can't help with it, really.

And thank you again for your help previously - your code is really what got me working again!!!

 

[bastanu]

Sorry for the delay in getting back, here is an updated version of the code (full disclosure: I did not write the original code, I found it on the web many years ago when I was dealing with form corruption in a .mdb file) in which I included the savetotext\loadfromtext for macros. I am not sure about the "user forms"
Here are some older posts dealing with this that might be of help:
Export All Database Objects Into Text Files | Access World Forums (access-programmers.co.uk)
Restore Database Objects (LoadFromText) | Access World Forums (access-programmers.co.uk)

Cheers,

 

[Marshall Brooks]

@bastanu - Thank you again!!! This will definitely help myself if this ever occurs again and others if they need assistance!

I don't know how to deal with the userforms from a script either - https://www.mrexcel.com/board/threads/import-userforms-using-vba-code.1071964/ - In Excel, they are apparently considered vbcomponents, but I don't know about Access.

 

[Josef P.]

I don't know how to deal with the userforms from a script either - https://www.mrexcel.com/board/threads/import-userforms-using-vba-code.1071964/ - In Excel, they are apparently considered vbcomponents, but I don't know about Access.

UserForms (lib Form 2.0) are stored in the VBA project as in Excel.

Export:

VBE.ActiveVBProject.VBComponents("YourUserForm").Export "..."

Note: Code and OleObjectBlob are stored in extra files.

 

[Marshall Brooks]

Any particular reason why you would delete the objects (tables) and not simply refresh the data in them using a delete\append query combination? I can see the need for what your doing if the table structures change, but for a data refresh I think is overkill.

Cheers,

I recently made two of these changes to the database.

• I don't check for existence of the tables anymore - they should be in there.
• I now refresh the tables instead of deleting them.

I didn't really see code that specifically addresses this, but I have a co-worker with a background in SQL and he helped me out, so I thought I would post the code here. There were 9 tables and an Excel File with a named range that get imported into a local table.

The old code looked like this:

    If TableExists("Work Orders") Then
        CurrentDb.TableDefs.Delete ("Work Orders")
    End If
    If TableExists("tblHolidays") Then
        CurrentDb.TableDefs.Delete ("tblHolidays")
    End If
DoCmd.TransferSpreadsheet acImport, 10, "Work Orders", "\\network path\Work Order Spreads.xlsx", True, "WorkOrders"
DoCmd.TransferDatabase acImport, "Microsoft Access", "\\network path\database_be.accdb", acTable, "tblHolidays", "tblHolidays", False

The new code looks like this:

CurrentDb.Execute "DELETE * FROM [Work Orders];", dbFailOnError
DoCmd.TransferSpreadsheet acImport, 10, "Work Orders", "\\network path\Work Order Spreads.xlsx", True, "WorkOrders"
CurrentDb.Execute "DELETE * FROM tblHolidays;", dbFailOnError
Dim strSQLinsert
strSQLinsert = "INSERT INTO tblHolidays SELECT * FROM tblHolidays IN '\\network path\\database_be.accdb';"
CurrentDb.Execute strSQLinsert, dbFailOnError

A few things that are not obvious:

• There is supposed to be a way to refresh the Excel Named Range into the table using SQL. I found some examples on the web, but didn't know how to make them work and my co-worker didn't either. Oddly, DoCmd.TransferSpreadsheet updates into an existing table, but DoCmd.TransferDatabase updates into a new table with 1 (or 2 or 3) at the end if the specified table already exists.
• DELETE * FROM clears the contents of the table, but leaves the table structure.
• Originally, the last line was "DoCome.RunSQL strSQLinsert". That worked, but for SOME users, it gave a warning about being about to append 74 records, etc. I changed it per http://allenbrowne.com/ser-60.html

I hope this helps someone else!!!