Acceess DoCmd OpenReport Not Working

[iamchemist]

Does anyone know why the fairly straightforward VBA command below works with some Windows 7 computers running Access 2007 and not with others?

The command is DoCmd.OpenReport "rpt_CardType_Ron", , acNormal

The broader question is that I am trying to update a few forms and add a few tables and reports to a fairly elaborate Access 2007 database Application at a non-profit Food Pantry. The actual Application has many tables forms, queries, etc.

When I add new or updated forms to the Database Application it seems to chose bigtime (though only on some computers running Access 2007). I seem to end up with a strange corrupted database, which can not even be copied from place to place successfully. It is critical that these modifications happen to this Database Application, and it contains many tables with important Client Data. The goal was to simply change a few forms, Reports, etc. to get it to still use all those same Tables, but present and collect data a bit differently.

Thanks,

Ron Cochran

 

[dhookom]

Is your application split into back-end (tables only) and front-end (forms, queries, reports, modules,...) files?

Does every user have their own copy of the front-end file?

Are your users connecting using wireless network connections?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[lameid]

Out of curiosity are the problem clients using Windows Defender? Do you by chance have API calls in any modules?

This stinks of some recent behavior I am getting in Access 2016 since my AV was changed to included Windows Defender.

Often the decompile command line switch will clear up some compiled code issues. The below in a shortcut may help... I usually browse to MSACESS.exe, make a short cut to it in my database folder and modify the shortcut from there.

"<office path>Msaccess.exe" /decompile "<path to database>"

I also have a more involved process that dies because Microsoft deems it suspect behavior now to export or import modules with api calls programmatically. No UAC just a no you can't...

 

[iamchemist]

Hi Duane,

Sorry it took me so long to reply. I somehow missed your post until today. But thank you very much for your reply.

You're exactly right, that I was attempting to import some Access Objects into the front-end of a split database. Each front-end User does have their own copy of the front-end split database. However, the database is split over a wired local Ethernet Network. Not wireless.

I now have a strong suspicion that trying to import Objects into the front end of a split database is exactly what was keeping the VBA Code from importing along with the Form. I believe that, if I put the database back together, everything will import OK. I had not posted that solution onto this Forum yet, because I'm not totally sure. I have not been able to give it a thorough test yet.

When we split this database, it split in an unusual way (as I understand Splitting, which is not very well). Instead of ending up with just Tables on the back end, we seem to have ended up with the entire database (including Forms, Reports, etc.) on the back end. I'm not sure if that is significant, here, or not.

 

[iamchemist]

Hi lameid,

Thank you for your reply, as well.

No we are not using Windows Defender. Luckily, this database runs over a wired, closed Ethernet Network. So, we have little need of Anti-Virus software.

Thank you very much for the decompile idea. I'll try that.

 

[dhookom]

Why are you importing objects rather than simply providing a new version of the front-end file?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[lameid]

I now have a strong suspicion that trying to import Objects into the front end of a split database is exactly what was keeping the VBA Code from importing along with the Form. I believe that, if I put the database back together, everything will import OK. I had not posted that solution onto this Forum yet, because I'm not totally sure. I have not been able to give it a thorough test yet.

There are many reasons to use the split database model.

Most significantly Forms and other non-table objects are prone to small design changes requiring an exclusive lock which causes a problem in a shared environment.
Next is sending the application objects over the network is just a waste of resources. Only data needs be transmitted, hence the back end or tables file is stored on the network for sharing.

So you should not combine your files.

As Duane was indicating by suggesting you distributing the front end; best practice is to keep a front end file that is the master development and application copy and then distribute that to all the users with updates. There are many solutions for this.

Personally I like the split model as when objects corrupt it tends to be in the front end file and those are easily replaced from the master. This keeps corruptions in files that contain data to a minimum but they still happen. So in the case where it does not work on some machines, I would first try replacing the entire front end on each machine. This is only a problem if the users have their own queries saved in the local copy. They should ideally have objects made for them and distributed or have a separate file for working with the queries other than the core application.

 

[iamchemist]

Duane and lameid,

Thank you again for your replies. There is a very good chance that I (a major Newbie to Access) are not going about this is the most efficient way.

The reason I am importing Access Objects is that at our Food Pantry (where this Application is actually run) the back-end database is located on a Networked Desktop computer that is being used more or less as a Server. The Front-End Laptops see that Desktop as a mapped Network Drive called Z:

When I bring the front-end software home to modify a Form or Query, there is no Z: drive with a back-end copy of the database (and tables) to link to. So, to work on it at home I have been importing all the tables to my copy, so it will run. That means that my modified front-end database can not simply be loaded back onto the front-end at our Food Pantry, because it contains resident tables and the front-end at the Food Pantry must be using Tables in the back-end version on Z:. Once I have made modifications to my complete front-end version (with all local Tables), Access (2007) won't let me delete those local Tables, so that I can simply copy my modified Front-End to replace the Front-End at the Food Pantry. It gives an error message, that says I must delete all of the Table relationships first, and even they would force me to delete all the local Tables (several dozen of them) one at a time. For that reason I have been trying to import the modified Access Objects.

Since I have read your replies, I have leaned that I can take a Front-End database copy with no Tables and import the Forms, Queries, etc. that I have modified into it. Perhaps I can just put that in place of the Front-End database versions at our Food Pantry. I will try that.

Do either of you understand why the Back-End copy of our split database seems to have ALL of the Access Objects in it? Not just the Tables. We do know that the Front-End software is using the locally installed Forms, etc., and not the ones back on the Back-End, because we have made several modifications to the Front-End, and the User does see those modifications, when they run the split database.

I know this is all kind of tedious! Thanks again for your help!

 

[dhookom]

You should be able to safely delete all but the tables in your back-end file (after making a backup). You take the back-end and front-end home to work on and use the linked table manager to link to where ever your BE is on your local PC. Make all the changes you need and copy your new front end to your work PC and use the linked table manager again to connect to the shared back end.

Most of us have some code to relink tables if they can't be found in the place where they had been previously. You can search this site (or others for the code).

If you want to make changes in the back end you must develop a strategy when everyone is out of the database. Make your changes and relink if you have added new tables.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[iamchemist]

HI Again Dwane,

Got it! Again, I really appreciate some advice from someone, who knows that they're doing.

I'll search the Forum for some re-link code.

 

[dhookom]

Here is just one relinking solution from the Access Web.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[iamchemist]

Hi Duane,

Thanks a lot for this code! I've been out of town for the past few days, so I couldn't thank you earlier.

So, when you are re-linking these tables (from a Front-End Database with the code you sent), I take it you just run the code directly from the VBA editor? Because the Front-End will not display Forms (in which I could potentially put a button set to run your code on a click event), because that Front-End database Form is linked to a table on Z:, which that database can't currently find. so, it chokes.

I plan to try all this at home tomorrow.

Thanks again for your help!

 

[dhookom]

I'm not sure if you noticed but there is another piece of VBA that must also be included for the "aht_" functions to work.

I actually use some different code but theAccessWeb was the first source of relinking code I found. In some of my older apps I would attempt to test a table to see if it was were I expected and if not the code to relink would be run. You should also be able to run from the immediate window by pressing Ctrl+G.

? fRefreshLinks()

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[dhookom]

I noticed some of the code includes APIs. These will probably not work if you are running the 64 bit Access.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[iamchemist]

Hi Duane,

Thanks so much for pointing me to the Relinking Tables code!. I did finally get it to work. It took me a while, because I didn't initially understand that the RelinkingTables function depended on 3 or 4 other functions present in the whole list of code, all in addition to the GetOpenFilename function.

I am running Access 2007 32 bit, so all the API's ran normally.

Thanks again,

Ron

 

[iamchemist]

Now that I have the RelinkTables function working, I see that it will relink to the Back End Database and Tables, that were originally produced, when the database was split. Our split Front End database normally relinks to those tables automatically each time the Front End database is started. Why would I need the RelinkTables function to ever perform that task?

 

[dhookom]

You are relinking the tables because you have different back-end locations from work and home.

When I bring the front-end software home to modify a Form or Query, there is no Z: drive with a back-end copy of the database (and tables) to link to.

I don't recall you ever stating before

Our split Front End database normally relinks to those tables automatically each time the Front End database is started.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[iamchemist]

Sorry. I probably didn't state that before.

Nevertheless, with your help I do now have a working ReLink Tables function I suppose I will just not run the ReLinking function, when we are connecting to the Tables in our normal Back End Database.

 

[iamchemist]

Duane,

I never stated that my Front End Database automatically links to the Back End Tables at our actual Food Pantry, because, as I did state before, the whole goal of getting this Table Relinking function to work was for when I bring the Front End Database home to modify, and there is no Back End Database in the correct path for the Front End to automatically link Tables to anymore. In that situation the Front End will not run and allow me to modify it.

But again, thank you so much for pointing me to the fRelinkTables function. It works great!