Alternative non Server BackEnd to Jet (1 Viewer)

Hi friends

I keep the main data in a server type back End in a LAN eg SQLExpress. In this multi user environment, to avoid too much traffic to/from the BEnd, i use .mdb to keep my temporary tables within the workstation which are used only by this particular workstation.

In one particular PC, i met occasional failure to access a selected .mdb; this is happening in Access.02 & 03. I have a feeling it is operating system issue. Do you agree or is it the inherent problem of .mdb?

Can you recommend a better alternative database that does not need a server service to work eg FoxPro. Appreciate your expert views.

Access doesn't need a server backend in order to work... but it DOES work better with a backend server than a pure .MDB backend.

Unfortunately, your question is a bit vague. "Met occasional failure" ... can you be more specific about how you know that it failed? I'm talking about specific symptoms or an error message or SOMETHING other than a vague description.

I have used front-end tables as temporaries many times, mostly when I was preparing a working list of data for an eventual INSERT INTO from the temp (FE) table to the permanent (BE) table. I've never had that fail unless the network connection failed. What is your network infrastructure?

Great Doc, there is no network involved with FE table. Like you, i use temporary tables in the FE to prepare data. The specific issue is even before i do anything, at the instance i try to access this temporary table, sometimes Access just hangup: saying the table is corrupted but it is really not. I try again the 2nd time is ok.

Since i sent out this thread i found something that i need to check. It has something to do with a hidden form with timer. If that is your cause, it is no wonder you never met a failure. Let me check it.

Meanwhile if you have to add, do stir my thoughts. Thanks.

Oh, I've been known to stir up someone's thoughts now and then.

Let's clarify even more. You say you get a "table is corrupted" message AND THEN on a second try you succeed. Do you take any action to do a Compact & Repair before that 2nd try? Also, is this table that claims to be corrupted in the FE or BE?


I'm beginning to think you have a procedural problem but can't yet put my finger on it. Typically (and I'm not saying that this is certain to be your problem), corrupted tables occur because of intermittent network connections to a shared BE file OR an abnormal termination of the Access FE such that a disk buffer didn't get written back correctly.

I am still not clear on the sequence of events that lead to this error. Maybe I don't want to know what happens AFTER you get that corruption message. I think I want to know what was happening BEFORE you got it.

Only a thought, it could be a time issuer, like data isn't fully loaded when you try access it.
But it depend of how you've it set up.
If you use code for getting the data into your database and manipulate it I would put in some breakpoints in the code, and wait 5-10 seconds before next step.
As wrote - only a thought.

I think JHB suggestion of timing issue do make sense; however i have played with sleep 2000 but still sometimes in occur.

PhDr, the FE table was never compacted and yet Access reports a corrupted table.

To be more exact, what i tried to do is to insert into a temporary table in the FE PC, data that i use to create a form that has combo boxes for selection of "From To" range. I do this within the Open.Event of the form

I now tend to think that i better insert data into this able outside the open.Evt ie well before i actually open the form.

Do you guys agree with this!!

FuzMic said:

... however i have played with sleep 2000 but still sometimes in occur.

Click to expand...

Sorry but sleep doesn't, (because it will not set the focus back to the operation system), either but breakpoints in your code and wait a little or use DoEvents.
Could you show the code what you use?

Every database ought to be implemented on a client-server architecture. The technology is ubiquitous, cheap and simple so there isn't much reason to do anything else these days. What is your objection to a server-based DBMS?

ButtonMoon agree with you about C-S Arch. The objective is for multi-user to use the common BE.

JHB Thanks for letting me learn that about SLEEP <> break. Are you saying i should use Application.Wait. I am not sure how to use DoEvent for this purpose.

My codes are essentially

1. At the starting form i use currentdb.execute "INSERT ...." onto a link temporary table TT located in the FE PC.
2. Then sleep 2000
3. Finally select data in TT to propagate for the RowSource of the combo box in another form opened within the Open.Event of the starting form

Hope this make sense.

A common back end in Jet is not a client-server database. My recommendation is that you use SQL Server, Oracle or PostgreSQL.

Moon noted!

FuzMic said:

..
JHB Thanks for letting me learn that about SLEEP <> break. Are you saying i should use Application.Wait....

Click to expand...

Listen, it will not help either, why are you not trying what I wrote about the breakpoints?
DoEvents is placed in the code right after a line of code which you expect to take some time to execute, such as inserting data into a table.
So place DoEvents a just after "currentdb.execute "INSERT ...." code line.
When I asked for the code it was not a general description of the steps, but the code itself.
Remove the Sleep, it helps nothing.

I'm either being very dense at the moment or the description is confusing me or I have totally missed something. Referring to posts #3 and #6 of this thread...

If there is only an Access FE file involved (per #3) and you get "corrupted" messages and then, without doing a compact & repair, the operation works on a subsequent try to do the same thing to the same table (per #6), this CANNOT HAPPEN. There is NO TIMING EVENT that gets involved here. You can set up a timer to delay you 10000 milliseconds or 1 millisecond or just leave off the timer - the result should be the same.

In the context described in post #3, Access is synchronous because while either Ace or Jet could operate multi-threaded, we have explored that ability in other posts. In essence, after lots of research, we learned that Jet or Ace will multi-thread for cleanup operations that can occur independently from the main thread, but you don't get to do anything else in Access until Jet or Ace (whichever one applies) thinks your tables are stable. In other words, if you execute DoCmd.RunSQL or a {DAO}.Execute, you don't regain control until the implied action is already complete. Any action query has updated everything and, at least for the DAO Execute case, the .RecordsAffected property is available to show how much was actually done. This .RecordsAffected count availability is not possible unless the action is synchronous.

FuzMic, what you describe makes no sense, which I interpret to mean that we have not learned the whole story here. Please understand that when I say "this cannot happen" - I am NOT calling you a liar. I do, however, suspect that your description is somehow incomplete. This incompleteness is the source of my confusion. There is something you are not telling us and without knowing what has been omitted, we are dealing with a major contradiction in behavior here.

One more question, and this might be a VERY long type of a long shot. This kind of error perhaps could happen if you put the DB files in a shared folder created by a network admin or a security admin rather than by you. Is this DB file owned by you and in a folder where you have at least MODIFY access rights?

Sorry gurus as a guy without formal training in this game, I can express my problem poorly or I may drop some key points that make my thread being senseless.
However the problem is intermittent, what actually happen is from memory, so key points can be dropped.
As I said earlier, it only happen in on xpwin.sp3 pc, so I have the OS on my list of cause. I will certainly carefully read what you guys have made effort to pen.

Meanwhile some answer to the points raised as I keep digesting.

Doc: The DB is not in a shared folder, I think FE user is the owner with full rights, thus i think there is security right issue. Moreover it only happens occasionally. I will double check this.

JHB: in your 2nd reply your key message is “breakpoints in your code and wait a little or use DoEvents”. From this I tried you suggestion by throwing away the Sleep and use Application.Wait. I then waited for your help to try your DoEvent, now you suggest to have the DoEvent after the Insert line; is this what you are saying or am i still dense.

FuzMic said:

..
JHB: in your 2nd reply your key message is “breakpoints in your code and wait a little or use DoEvents”.

Click to expand...

Both in my 1. and 2. replay I wrote about the breakpoints.

FuzMic said:

..
now you suggest to have the DoEvent after the Insert line; is this what you are saying or am i still dense.

Click to expand...

YES, if you don't want to try the using the breakpoints, then put the DoEvents as the next code line after the Insert line.
I don't know why you refuse to try the breakpoint method where you wait a little after each code line, because if that doesn't help the DoEvents doesn't help either. If you've tried it after my first replay, then you already now had known, if that was the right solution or not.
Or do you not have access to the problem computer just now?

Yes JHB, you misunderstand that i do not want to try the "wait". The fact is, the meaning of wait did not strike me because i never learnt it or know about it at all. From the google i thought your idea of wait is the break thus i came up with Application.wait as a bait to see if that is what you mean. So let say i want to try it how?.

I don't have that problem pc with me right now and even if i have, it does occur very often and that is my frustration of facing intermittent issue. I was just hoping to get some fresh avenues from my peers and to take time to tinker it later.

This is an interesting thread to refresh ideas from banana
http://www.access-programmers.co.uk/forums/showthread.php?t=167470

Kind Regards.

As I said earlier, it only happen in on xpwin.sp3 pc,

Click to expand...

The DB is not in a shared folder, I think FE user is the owner with full rights

Click to expand...

If this problem EVER succeeds without Compact & Repair intervention then drop the OS and folder permissions from your list. Both of these potential problem sources tend be "every time or never" with no middle ground.

However, one thing suddenly clicks. You don't do a C&R - but if you could open that database FE and check around, you might test whether someone has an AUTOMATIC Compact & Repair On Close - which COULD account for it failing once, then working after you close and re-open the FE. This still would not account for a behavior in which you can get a failure and without closing the database, trying again immediately and getting a success on the same exact operation. In the "Local folder owned by user, local table contained in the FE" case, the "fail then succeed without intervening close or manual remediation" is the thing that makes no sense at all. THAT is the thing that I said could not happen. I hope you can understand the nature of my concern that we are missing some facet of this problem.

Yes Doc, noted your concerns and your wanting to help fix the leak but i have to say i will need some time to click with your avalanche of thoughts.

Meanwhile and as always thank you for your the brain juice to flip flop on the elusive issue. :banghead:

A question on DoEvent; i am not 100% sure how to use it even after playing with the typical loop examples in the web.

My current question

If i use Docmd.OpenReport "myRpt1", acNormal, would placing an DoEvents immediately after it help my objective or better than Sleep 1500.

My objective is allow the current printing to be completed before i issue another similar Docmd.OpenReport "myRpt2", acNormal to another printer.

With all the discussion I am no longer sure what is the right way to meet my objective or is my objective valid?

Much appreciate comments from DCH & ....

If it is a spooled printer, you technically don't need a DoEvents. When you get back from the OpenReport command (for spooled printers) you are pretty much "good to go" with your next step.

You use DoEvents when you expect issues with asynchronous code; for example, sending an e-mail via CDO & SMTP, which involves a network operation in parallel to your Access work. That is because the device driver code isn't in your sphere of control. Stated another way, Windows manages it, not you. When you have that independence, you trigger a DoEvents call to "let things happen."

Other application objects can also be helped if you start an activity in that object and want to give it a chance to do whatever it is going to do. Also, to be honest, unless your system is heavily loaded with lots of concurrently executing parallel processes (and I do mean "Executing" as opposed to "in memory but idle"), you won't spend hardly any time in the DoEvents call waiting for something to happen because event signals don't usually take very long to process. Maybe a few milliseconds, tops. Therefore, adding a random DoEvent after doing anything major within an application object is benign.

If you have a heterogeneous DB app with Access FE and SQL BE (regardless of the flavor of SQL), DoEvent is extremely useful for queries that are executed in parallel on the BE server, i.e. set up as (I think it is called) pass-through queries.