INSERT INTO somtimes causes error

[joxo1]

Hi,

Can anyone give me any clues on this one?

using VB6 to write to an access 97 database,

I am using INSERT INTO to add a record. 5 minutes ago I was testing it and the code stopped with the error "The information could not be saved as it would cause duplicate values etc..."

Pressing F8 to step through that line causes the error however pressing F5 to continue (from the same line) causes no error and the data is written correctly??

Why can I not step through it but can continue it?

 

[hmckillop]

Is other users using this database at the sametime?

"I'm living so far beyond my income that we may almost be said to be living apart

 

[joxo1]

Hi,

Yes, there are about 100ish users of the system.

However, I have about 5 other systems that have a similar number of users and these systems don't have the same issue.

Let me know if you need any more info..

cheers for replying

 

[mmilan]

You may already have realised this, but it is claiming that you are trying to add a record that breaks a duplication condition on an index.

This is most likely the Primary Key index, so check the fields involved and see if that's the source of the problem. If not, look at other indexes for which you have defined "No Duplicates"

mmilan

 

[joxo1]

Your right about the error message, however my point is, why does it work when pressing F5 to continue with no error but pressing F8 to step through produces the error?

Assuming that the code was duplicating values then surley pressing F5 would produce the error as well?

 

[mmilan]

Dunno.

Why are you using Access in a 100 user, networked system?

Access can encounter corruption issues when used over a network - had no end of bother with it. I've also just completed a professional training course where we were told to never consider Access for any more than 5 users.

SQL Server is the way to go it seems, but if like me you're not made of money, consider alternatives like MySql and Postgres...

Something else you might consider is SQLite, but it's a similar model to Access - albeit claimed to have the same problems. It's performed very well when we've used it, but I would continue to advocate a RDBMS on a server client model...

mmilan

 

[VBrit]

Why don;t you download MSDE from microsoft. It is a cut down version of SQL server with no GUI, and less management tools.

It'll be easy to convert your Access databases to SQL server.

 

[hmckillop]

I have experienced the F5 causes error but F8 doesnt - generally though it means that the error isnt at the line you expected - probably somewhere else in the proc or function. How far through do you step with the code, are the inserts in a loop?
Can you give the code in the whole procedure?

"I'm living so far beyond my income that we may almost be said to be living apart

 

[mmilan]

If you check the licensing details, I think you'll find you can't deploy MSDE as part of your application.

You can't deploy MySQL either without paying license fees, but they're a hell of a site lower than MSSQL equivalents.
Postgres and SQLite are, I think, fine...

mmilan.

Ps. General rule of thumb - Microsoft are not well disposed to "giving something for free"...

 

[johnwm]

Check out this page:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnmsde/html/msdeforvs.asp

About half way down you will find:

In summary, the MSDE relational database engine exhibits the following characteristics and features:

* Is fully compatible with Microsoft SQL Server 7.0 and SQL Server technologies.
* Is freely distributable for solutions built with MSDE for Visual Studio 6.0 or Microsoft Office 2000 Developer.

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'

 

[mmilan]

I stand corrected.

What tools come with it?

mmilan

 

[joxo1]

Thanks for all your help on this one guys.

I think i've got all the info i need now.

cheers again.