Replies

Having autonumber keys have a couple of advantages over alphanumeric keys.
– You do not have to bother about a unique key, autonumber keys are always unique
– Searching numeric fields is always faster compared to searching alphanumeric keys.
– A numeric index is always faster than an alphanumeric indec.

You can allways define a foreign alphanumeric key (do not forget to put an index on it!) to ensure a search facility on an alphanumeric field.

Personally I allways use meaningless autonumber keys for all of my tables. I developed a wizard that generates a selection form, a detail form and all the code (a class module with database access to the table and a normal module with the form and control handling) based on the table definition. Working like this ensures a consistent design and a uniform user interface.

OK
Let’s put it on 100000 records.
It is a lot, but it can be done.
First make sure you have NUMERIC fields you select on. Make als sure that all the fields in the WHERE clauses of your queries are numeric and indexed.
Use the TOP quealifier in a query whenever possible.
Design you database structure well, ensure every table has a unique numeric key.
Prevent a table scan, that takes a lot of time! Use referential integrity!

You can have SQL server do this for you. You can put a trigger on the table and have a stored procedure checking whether a particular field is empty or not. If empty then the stored procedure can set the empty field marker.
This is very save way of working with SQL server because it also works if data is changed by another application then you Access application.

Oh boy, triggers, stored procedures, I realy hope they will come available in the next version of MS Access…..

24000 rows is no problem for access.
Make sure that when you want to retrieve your data, your selections run on an index and not on the main table, then performance should be no issue at all.

Hi Ken,

Good question.
In the organisation I work at the moment Access is seen as an enduser tool. Imagine all the [censored] they can produce….

Why not using Access? You install one environment and can you can make nice looking applications based on a pretty good database system with good looking forms, good reporting facilities and integrated security.
Try that with VB6!
The only problem is that if you want to make a nice looking and nice performing application you have to have a good knowledge of relational database design, putting the right indexes on the right fields, preparing queries.
You also have to know about transactions, user interface design, batch processing etc.
As you see, all the knowledge a good projectteam has to have in a normal organisation.
That is what a lot of people forget when starting with MS Access. MS Access is a complete environment to create applications!
If you want a good application, you have to have the skills to design and build a good application! A lot of those skills are not related to a particular environment.

I still think MS Access is a great tool to work with.
I designed several high volume applications, made couple of applications in a 100+ user environement, they still work perfectly. It all is possible in access, but you have to DESIGN them.

Hey,

You are not the only one who found out the hard way.
In a lot of cases I even choose to program the database activities (UPDATE, INSERT and DELETE) completely in a class module to ensure every field is filled with at least an empty string (“”) or 0.
Yes, I also think this is one of the famous ‘Gotcha’s’.
Beware all, there are more…..

Hi Patricia,

A Null value is indicating that the data is missing. In that case there is no data!
That is the reason you cannot use the = sign.
You can use if (expression) IS Null or the function IsNull(). I never noticed any difference in using them. Using the function gives you in most cases a better readability of your code.

Hi,

Rory suggests to define a new database in access 2000.
We probably had the same thoughts.

You can also define a new database in access 2.0 and import all the components (tables, queries, forms, reports, modules and macro’s).
Try if the new db works well.

If you stick to Access, working with Null values is no problem. If you want to migrate to other DBMS-es, you may bump into a DBA who is only implementing fields that do not allow NULLS.
Also there are (not much) some database systems that won’t allow NULL values into their fields.

Then you will have problem….

But: hey, what’s wrong with Access, works fine for me and my clients!

Hi Andy,

Queriosity is a great quality, you can learn a lot…
I put several function in the DLL, which are called in quite a complex batch process from VB. I use some statics to store data in the DLL that is not needed in VB. Working like this prevents sending that data from the DLL to VB and back without using it in VB.
Because the batch proces is processing a lot of transactions it is no option to remove the DLL from memory and bringing it up again for each transaction.
And yes, I already programmed an initialise function in the DLL, I did not see another way (although I still think there must be one).

Thanks for the help.

I am not sure if I understand the problem completely, but I’ll give it a shot.

If you want to retrieve the data, I suggest you use a query and calculate the dates in the query.
The results of the query can be used for reports and forms (read only!).

If you want to store the data, just start writing some VBA that will do the job. This will give you full control of anything you want to do.

Can you describe the problem you have in more detail, include coding, SQL statements etc.?
I think we can help you better that way.

Hi,

First the obvious solution. Did you try opening the mdb with the SHIFT-key pressed?
Pressing the SHIFT key while opening the mdb will prevent the autoexec macro to be executed.

Did you ask your vendor for the accessable source code?
Did you ask your vendor who is continuing the support for your application?

If the mdb is password protected you can purchase a lot of products to recover the password (for instance from elcomsoft). Search the net with the keywords access and password, you will get a lot of hits.

Hi Andy,

Thanks for the reaction.
Unloading the DLL is no option, then I have to reload it again about 50.000 times.
I just want to reset the static.
I solved it the moment by adding a new function to the dll, initiate.
There must be a better way, once I will find it…..

If you use the INSERT statement like this the fields in both tables should have the same definition. If not, you can get a variaty of nice error messages.
Consider rewriting the insert statement like this:

INSERT INTO TblDefunctFootages
FIELDS (fld1, fld2, fld3,…)
VALUES (value1, value2, value2, ….);

You can control the INSERTS better and the code becomes more readable.

How do you execute your SQL statement?
If you do it like this, you can trap the errors:

dim db as database
set db = currentdb()
db.execute strSQL, dbFailOnError
set db = nothing

The dbFailOnError ensures that an error is raised in case the SQL statement fails!

You included this code:

Do Until TransTB.EOF
PayeeTB.AddNew
PayeeTB![Payees] = TransTB![Payees]

This is not complete! Looking at this code I think the following is happening:

There are two recordsets:
PayeeTB and TransTB
For each record in recordset TransTB a record is added to recordset PayeeTB.
The content of the field payees is copied from recordset TransTB to PayeeTB.
There must be more code, this is not complete!