Question Ac2003 vs Ac2007 and FE/BE split (1 Viewer)

[The_Doc_Man]

Here's the scenario.

Dept. of Defense site, so the peons (read contractors) do not get to set policies and such on their machines. I have a database that has been split to FE/BE configuration. Both are .MDB files right now. MDE down the road, perhaps. BE is (at the moment) pure tables. Later I'll add code to stop folks from direct-opening it, but right now, that's a future consideration. The FE has queries, forms, reports, and code. No macros. It was developed originally in Ac2003. I'm now using Ac2007, but I've never saved the DB in 2007 format. It still tells me it is Ac2000/2003 format.

So, I load up the FE and BE to a shared area, with code in the FE that if someone actually tried to run it from the share, it would detect something was wrong and would barf. (But it would TELL the user to make a local copy on his/her workstation hard drive before it barfed.) The table link manager confirms that I can see the BE just fine from the FE.

I've tested it using Ac2007. Everything works fine for me except a few forms that are still under construction. They wouldn't run correctly for anyone, me included. I'll fix that, too. I have put code in the DB to decide which version of Access I'm using, 2003 or 2007, and don't try to disable the ribbon or object panes in 2003. In 2007, this works like a champ.

OK, so prepping the FE for sharing, I define the staring form which is just a big old switchboard. When that launches, I run code behind the switchboard in its Form_Open event to query the terminal as to who is running the session. We get this info from our domain-based login so there is no dialog. I explicitly DID NOT try to implement workgroup security in this DB. Everything looks at who you are and your role code (which it gets from a lookup table: DBA, Guest, Sys-Admin, etc.) Therefore I did not try to implement stuff according to owner ID. If it weren't for the filtration code that intercepts the Form_Open(cancel) event on the switchboard and all other forms, the DB would be wide open.

I have logging code in that _Open event to tell me who is logging in by their domain ID, what is the computer name, and in what folder they were opening the FE file. It logs me perfectly whether I am using the wide-open version of the FE or the version that has been compressed, repaired, and otherwise secured with respect to things that it allows. (All the stuff that, in Ac2007, is found when you click the Windows icon, Database options, and set up "Current database." That explicitly includes "Preferred mode Open Shared")

Logging still works for me correctly if I update the tUSR table to change my role to a non-DBA. I also have digitally signed the code in the "prepared" copy of the DB just before I copy it to the shared area.

So I'm showing this to my boss. He clicks on it but he has Ac2003. He cannot get the switchboard to open automatically on his terminal even though it works for me. The window changes names according to the Title info put into the database when I prepped it. But NOTHING logs. Not even the event log that doesn't try to look up who you are in my tables, it just logs the raw event of computer name, username (from the terminal), database file name (which gives me path). That is the very first bit of my VBA code that should execute, but it does not.

After thinking about this, I checked my boss's Windows rights. Full Control, no problem there. The file will not open correctly for him. So now I'm trying to figure out what is different from his terminal to mine other than Ac2003 vs Ac2007. I'm trying to figure out why it opens for me but the same FE goes bonkers for him.

I'll revisit file-level security Friday when I get back to work. In the meantime, does anyone else have a suggestion as to where to look? This is driving me nuts because I explicitly avoided using anything related to Workgroup security. I am rolling my own security all the way and as far as I can tell, it is working correctly.

I'm open to suggestions. (Since this forum can be publicly searched from Google, keep the suggestions clean, please.)

 

[gemma-the-husky]

just a silly thing - sometimes the forms are not visible, because they sort of scroll off the visible screen. Also check your bosses macro security level, in case its refusing to run code.

while you are testing, put some msgboxes in the open event - see if they are running.
put some code in there to identify the open forms.
try setting a breakpoint, so you can walk through the statements.

I am sure its a "doh" moment - something trivial but maybe not so obvious, when you crack this.

 

[The_Doc_Man]

The behavior is not that of scrolling off-screen. I found some code online that calls window control stuff from the Win32 API to maximize windows and such. I would think that I would see something other than just a title bar. I'm not trying to turn off the ribbon and navigation stuff in Ac2003 (Access 11.0), just in Ac2007 (Access 12.0). On my system, I single-stepped through that code after getting Access.Application.Version just to see what I really had.

I am going to put the "untailored" version up to the shared area and let him download that to see if he can open the DB when it doesn't have a defined startup form. Then I'll manually trigger the switchboard with a breakpoint at the first line of code in the switchboard Form_Load routing and trace that in debug mode.

After mulling over this problem last night, I begin to wonder if the problem is that his machine can't see the BE where all the logs would go, which is why it wouldn't log. But the really kinky part is that he CAN see the file server where the BE resides because he downloaded the FE from it. Also, after his machine failed to open the FE properly, I aborted what was going on and manually direct-opened the back end to look at the event logs.

Of course, the real question is not whether HE can see it, but whether Ac2003 can see the BE at that point. I can prove that by opening any table through the raw FE.

I agree, this is going to be a DOH moment when I find it, but DAMN why does it have to be so bloody elusive? I've got tons of work to do on some of the more formalized queries and reports for status presentation to external parties, but this is the last really big hurdle before beta release and in-house usage as a test. I hate late-arriving brick walls like this.

 

[vbaInet]

Just wondering if you split the DB using 2007?

It might be an idea to split it using 2003.

 

[The_Doc_Man]

This problem is now solved, but OMG what a mess, and what incredible implications for us poor schmucks trying to hide the [SARCASM]"helpful" [/SARCASM] Office 2007 ribbons and new navigation features.

I dropped an "unsecured" copy of the FE onto our shared drive. This one doesn't have a startup form and doesn't disallow the F11 or other shortcut/special keys, so none of my security clicks in. My boss downloaded it. He's still on 2003. He can open it and see data. No problem. The linked tables open reasonably fast. So... I have him manually open the switchboard, and voila! (or eureka, for those of a Greek bent.)

In trying to make the DB run correctly with both 2003 and 2007, I have to test for the version of Office you are running. If and ONLY if you are running 2007, I have to turn off (hide) the ribbons and object panes, which is the 2007 way of hiding those objects. BUT... one of the methods required in turning off 2007's features DOESN'T EXIST in 2003 , it's only in the Office 2007 libraries.

In essence, this was a references issue that was not properly trapped and terminated. Because of the security, there was no way for this problem to be correctly handled, either. It looks like what was happening was a trap loop. When you open the form and the references aren't "right" something happens that my trap code somehow doesn't trap correctly. Because it is not handled correctly, the form cannot open up.

You cannot even COMPILE the code in 2003 because the .NavigateTo method doesn't exist in the 2003 reference libraries, but (so far as I can tell after a long search) you need that method to turn off the 2007 object navigation panel. You have to have it to be able to navigate to the particular panel so that you can then do a "hide" operation while still inside it.

Because I have the forms designed to hide all database window icons, controls, and intrinsic operations, the solution appears to be one of two options:

1. ALL of my users must upgrade to 2007 or
2. I must maintain TWO COPIES of the FE, one for each version of Access/Office, because the mechanism for hiding the Access features including the DB navigation window changed incompatibly between the two versions.

This wouldn't matter if it weren't that our forms are intended to go full-screen to hide the DB window. If you don't hide the object panel and ribbon, Access 2007 itself goes full-screen but the forms appear in the rectangle left over after the ribbon and object bar carve out their "shares" of the full window. Of course, we don't want them to have a share, which is why we were mucking about with closing that stuff anyway.

My boss and I have discussed this. The Office upgrade was mandated by the Dept. of Defense powers-that-be, so we are going to have the bite the upgrade bullet before we deploy this DB internally. We had hoped to put off the upgrade, but now we don't have ANY choice. In this case, because we are making a "closed" solution where Access controls are of limited presence at best, it is an "all or nothing at all" solution. The boss doesn't want to incur the overhead involved in having a dual-copy situation.

I'm going to find a corner somewhere and lick my wounds while whimpering piteously. I am decidedly NOT a happy camper. I'm glad my parents taught me to forgive, because otherwise some MS programmers would have wounds to lick as well, if I could get to them. I don't care WHAT their problem was, that is a TOTALLY unkind thing to do to a developer trying to constructively use their frimpin' product!

 

[The_Doc_Man]

BTW, vbaInet, I did split it using 2003. I really don't think that was a contributing factor. I'll know if I have other issues after my boss upgrades to 2007. If HE can run it, we are good.

 

[Banana]

That's a PITA.

I wonder, though if using compilation directives would save you.

Basically, embed everything that's 2007 specific in a block:

#If Is2007 Then
  '2007-specific code goes here...
#End If

The trick, though, would lie in how to set the Is2007 accordingly:

#Const Is2007 = ???

For a regular Const variable, we can set a constant to another constant, so this is ok:

Const a As String = "a"
Const b As String = a

but this isn't ok:

Const c As Date = Now()

I don't have Access right now in front of me so I can't check this. I also can't remember if it would be legal to do thus:

#If Application.Version >= 12.0 Then

Really wish they gave VBA a better preprocessing capability. That would simplify this kind of situation.

 

[The_Doc_Man]

Banana, we just decided it was time to bite the bullet. We were under a mandate to upgrade anyway so we're ALL going to upgrade. I didn't realize VBA even ALLOWED such directives, though I've seen other languages with that ability. But, you see, that means that I have to allow a late (essentially, run-time) recompile, which in turn means another delay in my opening up the forms in question. Yes, I can do that, but would I want to?

 

[Banana]

For better or worse, there's something to be said about having an uniform environment to deploy the product.

You're right that we'd need to re-compile prior to the deployment but at least that could be done at install-time rather than at start-up. I'm thinking of packaging it so one can then run script to force a recompile once installed. That way, we don't need to suffer a delay everytime we start the application up.

But that's academic right now, anyway; at least you know that VB has some support for preprocessing. Not as powerful as C's preprocessor but at least it's there for taking.

Best of luck with the upgrade.

 

[The_Doc_Man]

You're right that we'd need to re-compile prior to the deployment but at least that could be done at install-time rather than at start-up.

Banana, my friend, unless we just passed each other in the "understanding" aisle, I cannot pre-compile in the mixed environment. I wouldn't know until the individual user opens the local copy of the DB whether I was looking at Ac2003 or Ac2007 as the Office platform. If I'm on Office 2003 and the DB was compiled under Office 2007, I'm going to have reference issues. (I know 'cause I've already had them.) If the DB was compiled under Office 2003, on the other hand, I'm recompiling the code that I'm running, which brings to mind the image of the old Looney Tunes cartoons where the guy sits on a tree branch and saws it off while still sitting on it. Besides, now that I have converted to Office 2007, I've lost the ability to do a 2003 compile.

Maybe it is for the best that we force everyone to the new version. Doing otherwise would simply be fending off the inevitable.

 

[Banana]

There's no denying that forcing everyone to use same version is simpler than trying to get it to work across versions (and one could make the case that it may be simpler to maintain two copy of front-end copies compiled accordingly then packaged).

My idea may not have been all that clear- I was envisioning that one would use some kind of installer... even just a simple .BAT file will do... to not only place the Access application but also verify the references and thus do a compile right there on the user's computer - which was what I was referring to "install time", something that isn't built-in to Access (unless one uses Packaging Wizard but I don't know if it "recompile" but it does take care of the references fix-up which seems to me imply a need to do a recompile).

At least with 2007 onwards, Packaging Wizard is free (as an Add-in for 2007, built-in in 2010) so there's that for references fixup. That is an improvement from the unfortunate situation in 2003 and earlier where one had to pay extra dough just to have such functionality as well as the runtime engine.

Obviously that's more involved than requiring everyone to use the same version but well that's the solution I was thinking of. Hopefully this helps someone else who later find themselves in same shoes as you are and thus make an informed decision.