Browsing the MS Access category

MS Access VBA Environ() function not defined

A user was getting an error that the “ENVIRON()” function was not defined.  This is a built-in function, so I was confused at first…

In the VBA code, I went to Tools >> References and I saw that there were some missing libraries.  What happened was, the current user was on MS Office 2010, and someone else opened up the database in a newer version of MS Office.

In the “References” box, I saw that it said “MISSING: Microsoft Excel 15.0” .  MS Office 2010 equates to version 14.0

Access was “smart” enough to upgrade the library that was needed when the other user opened it up in MS Office 2016.  Then when the original user in Office 2010 (Version 14) tried to open it, it couldn’t find the reference (Version 15).

The solution was to update the reference (uncheck the version 15, re-check version 14), and tell users that if they’re using the new version of MS Office, to use a different instance of the database.

Read the article

MS Access “not a valid Win32 application”

I have a coworker who was trying to open an MS Access database.  He got the error above: “[path]\database.ldb is not a valid Win32 application” .  The problem was that he was trying to open the .ldb file instead of the .mdb (or .aacdb) file.

When a MS Access file is opened (the .mdb or .aacdb), a new file gets created: a “locked” database file (“.ldb”).  With our database being on a shared drive, when someone else went into the database, the .ldb file was created, and the additional user tried to open that one, instead.

Separately

This coworker got another error:

“Something unexpected went wrong with this URL: file:///\\file\path\here\database.mdb  is not a valid Win32 application”.

The user was on a newer version of MS Office (version 2013).  The database was created with an older version.  For whatever reason, MS Access / the operating system wasn’t set up to open a “.mdb” file.  The solution to this was to open MS Access independently, and then from within MS Access to open the database.

Read the article

ms access the expression on click you entered as the event property setting produced the following error: Return without GoSub

I got the above error.  I’m not sure what caused it.  I did a “Compact & Repair” database, but that didn’t help.  I deleted the button (on the form) I was pressing, and re-added it in (with the same name – thinking there was some property of the button that was causing the error).

What I ended up doing was, in the code, Debug >> Compile.

That somehow solved it!

Read the article

MS Access “This object was saved in an invalid format and cannot be read”

None of the solutions I found online worked; I ended up having to restore the database from a backup copy.

Read the article

MS Access – VBA Form Refresh Dataset

I had a form in MS Access that was bound to a table.  I needed a button that would create a duplicate record and then go to that new record.

The solution was:

Form.Requery

DoCmd.RunCommand acCmdRecordsGoToLast

Note: Form.Refresh did NOT work – I had to “Requery” it instead.  By doing a “refresh” (and going to the last record), I saw the default value of a new entry – without the form realizing that the database table contents had changed.

Read the article

MS Access database size changes

A coworker needed a copy of their MS Access database.  Rather than “copying/pasting” it, they did a “Save Database As”.  They noticed that the file size of the database was drastically reduced, and thought something was wrong.

Everything was ok!

Occasionally, you might want to “Compact and Repair” a database to remove any temporary data.  This is especially useful when your MS Access database is approaching the file size limit (of 2GB).  Apparently when you “Save Database As”, the Compact and Repair is automatically run – thus reducing the file size.

Read the article

MS Access linked table takes awhile to run a query

I have an MS Access database that has a linked table to an external source (specifically, Oracle).  There were 2 tables; one had a composite primary key, the other table didn’t have any primary keys.  When linking the tables together, the query ran terribly slow.

 

The solution: I found out what the primary key should have been (on the table that didn’t have any) and added it in (deleted the linked table, and reestablished it).  The query ran much faster!

Read the article

MS Access ODBC Connection mystery

I have an MS Access database with local tables, stored on a shared network drive. There were people in a physically different location that had trouble querying the data (it took too long).  If the data was on a SQL Server (and linked through MS Access), then it would be faster.  I created an ODBC connection – with NT Authentication- to the data.

 

I then made a pass-through query – using SQL Server authentication (where I hard-coded a username/password)- to connect to the linked table.  As soon as the initial connection was defined, users should (in theory) be able to access the linked tables.  This wasn’t the case: even though users were able to run the pass-through query, they were unable to get to the tables.

 

The solution: ensure that the initial ODBC connection is through SQL Server authentication, then re-link the tables using this connection!  Even after I changed the ODBC connection from NT Authentication to SQL Server authentication; it didn’t matter – MS Access had remembered how the link was set up.

 

With this in place, I was able to, on the initial form load, open the query (that has the pass-through), and then close this query – all without the user knowing it.  Then the user was all set to use the Forms as normal (I simply renamed my linked tables to what the old, local, tables were).

Read the article

MS Access opens up new entry when doing a search

I had a form in MS Access to enter in new data.  The same form is used as a “search results” (from a different form).  The problem was that for new entries, I had code that the Form Load was defaulting to a new entry.  This was causing a problem with the search results (as, I don’t want to go a new entry).

 

The solution: use OpenArgs to specify if you’re coming from the search or not, and add in the code (or not) that jumps to a new record.

Read the article

MS Access number of fields limitation

MS Access has a limit in the number of fields that could be in a table (255).  While using MS Access, and I was linking (via ODBC) to an external source (as a “linked table”).  This other source had more than 255 fields, but it only brought in the “first” 255.

The solution: create a pass-through query, and in the SQL, explicitly state which fields you want.

Note: you may have to change the SQL slightly (specifically, put in a “.” instead of “_” since you’re using the version of SQL native to the database source, and not the MS Access version).

Read the article