Issues with MS Access 2k db as Enterprise Database 2

[insanevbr]

I am having a problem with an MS Access .mde returning an error stating 'OLE Object Error' when I am attempting to return and update a value from an MS Access mdb table on the Server.

This is driving me mad because I can't trap the error in the mdb because it occurs only after compile to .mde.

Anyone have any similar experiences and a possible remedy?

Side note: The original Data Layer was MS SQL Server 7, but the customer didn't want to invest in the Server upgrade so I down sized it to MS Access and converted the app to a Thick Client design. App works perfect when it was with MSSQL.

Thanks in advance!

 

[JeremyNYC]

Can you post the code that's falling over? It's a little hard to tell what's going on from such sparse info.

Jeremy =============
Jeremy Wallace

http://www.AlphaBetCityDataworks.com

Designing, Developing, and Deploying Access databases since 1995.

 

[insanevbr]

JeremyNYC:

Thats just it, the code is solid. I have used both ADO and DAO with the same results. It runs without defect as an MDB, then when I compile into MDE and install, it blows out on ANY Save, Update or Populate event or function. I should also include that I am calling it through a custom interface comprised of an ActiveX ListView and TreeView. The nodes launch a form form a specific MDE based upon a command line in a basInterface module in the MDB/MDE.

Here is the problem: When I reinstall and test with a Thin Client and have stored proc do the work, it fires fine, no defects in the MDB or the MDE versions. As soon as I switch back to a Thick Client with an MS Access mdb as the back-end, it fails in the MDE version only. The MDB works fine. I have installed the most recent MDAC sp as well as all sp and updates for their NOS and OS's and MSO. I know that the code is tight because myself and 5 separate engineers have tested and verified it. I am wondering if anyone on this board has encountered the same issues with MS Access as CLIENT/SERVER? I have looked in the MS KB without any action so I am spreading out from there.

Thanks.

 

[jgpasko]

Insane:

I am assuming you are running a file/server with a front-end/back-end and not a "true" Client/Server. I had this problem to a degree when I was using MSA 97. Have you tried creating an adp? I am not quite sure what your total architecture is, but I am assuming that you are using the mde as a faux object. If it works as an mdb but not as an mde, just secure the mdb and run it that way. Or do you want the code stripped out? I don't know. Anyway, you're right, it won't work as you have it. You need to use it as it was originally configured as Client/Server with an MSDE or MS SQL Server as your db. If you want to have greater security and code control use VB.

Good Luck John Pasko
john@rts-sd.com
"No matter where you go, there you are."

 

[insanevbr]

OK, that leaves me right where I still am. I KNOW that it doesn't work and I have to use an alternative approach. I could probably use the MDB and secure it, but there is always a bypass if a person knows even rudimentary VBA and has a book. This is an Enterprise MRP application and the client "doesn't have the budget" (Read: Doesn't value technology) to invest in MS SQL Server. I still need to know if there is som potential work around on the MDB/MDE issue? Is this even a known bug? I have 27 MDB's that I compile out into MDE's and call various forms and reports and class modules using their Public Functions as command lines to execute from when an iconized ListView is clicked.

Is this the problem? Remeber though, it works WITHOUT defect as an MDB or as an MDE as a CLIENT to MSSQL7.

As far as coding it in VB, it would be another 6 months to convert from MS Access to VB, Test and deliver. I don't have that time available. This project is already over due because of this flaw (And the Client's ever changing requirements)

Peace

 

[Junior1544]

I know when I first started going with mde's instead of mdb's I was having a problem that i would get error'd out of alot...

Look at your code to see if your code tried to edit the format of a form... or if it even tries to go into design mode of any object an mde will error out... i don't remember the error's exacly, but once i figured it out i started changing things slowly untill i could run it as an mde...

the reason I went slowly was I was learning vba at the time, and was in a live use enviroment so I had to test every thing extreemly well...

--James
junior1544@jmjpc.net
Life is change. To deny change is to deny life.

 

[jgpasko]

Insane:

Here's as question I have;

Are you using a mix of ADO and DAO? You mentioned previously that you used both DAO and ADO in your application and that both worked. Which library do you have as upper level? DAO or ADO and are the lib's current version? This could be the problem.

How many users will this app have anyhow? Although the documentation states that MSA will support 25-50 users I wouldn't entertain a File/Server config with anything more than 5 users, especially if there are going to be large frequencies of transactions.

Good Luck. B-) John Pasko
john@rts-sd.com
"No matter where you go, there you are."

 

[JeremyNYC]

OK, I have to jump back in here.

First of all, John, I've got a pure Access MDE front end to MDB backend with 20 users hitting it pretty hard all day long. And it works fine, thanks very much. I've heard regular reports from people I respect of having as many as 50 users.

If your code and your table scheme and sql are done tightly this stuff works. It works for me and it works for many others. There's no need to be shy about having more than 5 users.

Next, Mr. Insane, your code works in some situations but not in others. I don't doubt this for an instant. I've seen it before. There are ways fo working with word files that fail if your file is _named_ .mde, but will work if you change the name to .mdb, even though it's still an .mde file. I asked you to post the code that falls over because maybe it's something that someone here could recognize as problematic. Or maybe it's something someone could take a guess at how to fix. But you declined.

James clearly has some experience to offer here. I have some experience with these issues. I'm sure many, many other people do, too. We're more than willing to give you whatever help we can, but we can't do that with the amount of information you've provided.

Jeremy =============
Jeremy Wallace

http://www.AlphaBetCityDataworks.com

Designing, Developing, and Deploying Access databases since 1995.

 

[jgpasko]

Whoa there Jeremy,

I was stating my opinion. If Insane is developing an "Enterprise" Material Requirements Planning application he probably should sell his client on the SQL Server for many different reasons, scalability and increased security being two of them. I don't doubt your level of experience or the credibility of the other developers you talk to regarding MSA and its capabilities. This is just one programmers opinion (mine)on what maybe a configuration issue.

Not defending his irritating demeanor, but I think Insane is frustrated at the least and has ruled out the problem being a code issue and more an architecture/platform issue.

No offense intended to MSA developers (I am one) or VB developers (Am one also). Just offering my opinion and hoping it may add some light.

It all comes down to personal preference.

John Pasko
john@rts-sd.com
"No matter where you go, there you are."

 

[JeremyNYC]

John,

Yeah, you're right--I was too harsh, and I apologize for that.

My point was that I do not think that the code has been ruled out as a source of the problem, because I have seen (created) problems with code that works fine in an mdb and falls over in an mde. And also that I am not convinced he is using the wrong tool for the job, as I have seen applications with plenty more users than five.

But that doesn't mean I have to be snotty like that, and I apologize.

Jeremy =============
Jeremy Wallace

http://www.AlphaBetCityDataworks.com

Designing, Developing, and Deploying Access databases since 1995.