Can I do this in Access?

[Elizabeth- MDC]

About 3 years ago I built a loyaty card database for use in our two malls.
Front end, back end with replicas. It's robust enough. It stores customer
info and card numbers and tracks visits for different promotions. Ex. The
Visit Table records Customer ID, date, event ID, and kid's id if it is a
child linked to that account. It issues warnings if they have already
participated or are tryting to get extra gifts they are not entitled to.

Not the easiest thing I've ever done, but it works for us even though it
gets corrupted about every 6 months, and the searches are getting a bit slow.

We want to go to a points based system next year. The idea being that when
we scan your customer card, we can then take your receipt and record the
amount and the store it is from. Based on certain promotions or rules, both
the points awarded and the actual sales amount and store ID will be recorded.

I CAN code the neccessary queries and VB language to accomplish this. My
concern however is that with a customer list of approx 10,000 we'll easily
reach a "visit" table of 1,000,000 in two to three years.

I've looked for out of the box loyalty card software programs and everything
is either way too expensive, or does not record the data we need to capture.

How do you recommend I, A) Deal with a database this size, and B) secure
said database? Is Access a proper venue, or should I look into other
solutions. I am capable of learning new languages, so unless I need to go
back to school to learn this, then I'm willing to give it a try.

 

[Albert D. Kallal]

You don't mention how many users you have for this database at the same
time, but I see little reason or problem with a million rows. A database
with a million rows is not really that large.

Just yesterday I was doing migration data for a client of mine and the table
had 500,000 rows in it. In fact I found the use of this table far more
snappy than the same table when migrated up to SQL server.

That table with a half a million rows was quite easy to work with, and I
found little performance problems if any, and I believe the same would be if
the table had a million rows.

Don't confuse the development tool called MS access with that of your
database engine. You can install MS access on thousands of computers, and
have
all those computers hit the same database on the back end at the same time.
As long as that back end system is a capable SQL server set up for many
users, then MS access will work fine in this case.

You also might have the case where it's not the number of users, but your
table sizes are starting to get rather large. Again the solution here is to
contine to use ms-access and simply use SQL server for the backend data. So,
you have to "choose" what data engine you going to use with access. That
data engine might be JET, oracle, MySql, or SQL server.

So you have many avenues in which you can plan for growth. It's not really
that you'll have to change the use of MS access, the question will be what
kind of server you're going to use to hold the data.

If you expect to have talbes with common 10 million rows, or even quite a
few tables with a million rows in the future then at that time you can start
using SQL server on the data side, and continue to base your front ends that
you have now on MS access.

Not knowing your setup, I have to assume that this some type of server you
have on your system now where you place this data?

It's also not clear if you are using replication because each kiosk (or
whatever you are using) does not have a connection to your network where the
server side is? If you can ensure some kind of connection to the server,
then you not need bother with replication at all.

 

[Elizabeth- MDC]

Thanks to both of the answers so far. The easiest part of this problem is
the fact that at most three people will be using this at one time, with
different databases for each mall. I figured I had to do sqlserver to make
this happen. Time to read up a bit.

 

[John Graham]

50 million is too many for Access. I tried that as an experiment.
1 million might be OK, if the records are small. I would think
that would probably be at about the limit. If it works reasonably
well, it is likely to be faster than SQL Server. However I would
expect that if it runs out of memory or cache it is likely to be slower
than SQL Server: SQL Server is designed to run in situations
where there is much more data than memory.

As long as you are doing fairly simple things, upgrading to
SQL Server at some future point should be painless. SQL
Server is free for small systems, so you can test as you go
along, check that it works in both systems, and decide which
you want to start with.

(david)

 

[David W. Fenton]

The searches will slow down as you get more rows in the search
tables with the guid columns.

Where do you get the idea that GUIDs are in use?

 

[David W. Fenton]

=?Utf-8?B?RWxpemFiZXRoLSBNREM=?=

The easiest part of this problem is
the fact that at most three people will be using this at one time,
with different databases for each mall. I figured I had to do
sqlserver to make this happen. Time to read up a bit.

If you need a combined database (i.e., all three malls having the
same data), and the data is being input at three different
locations, you're going to need a form of replication either way,
unless the editing is going to happen on a computer with an Internet
connection. In that case, you should consider hosting the app on a
Windows Terminal Server so that you keep all your data in one place.
Then, regardless of whether or not you stick with a Jet back end or
upsize to SQL Server, you'll not have to worry about any
replication/synchronization issues of any kind.

 

[David W. Fenton]

50 million is too many for Access. I tried that as an experiment.
1 million might be OK, if the records are small. I would think
that would probably be at about the limit.

I think that's ridiculous. Number of records is *meaningless* --
what matters is how much data is in those records, such that you
could end up bumping up against the 2GB file size limitation. A
table with a small number of numeric columns could very easily have
50 million records in a Jet db and cause no problems at all,
assuming it's properly indexed.

 

[David W. Fenton]

The s_GUID column and the three other replication columns are in every
replicated table. Compare it to the search times on nonreplicated tables
with many rows.

I know about the s_GUID field (I have a tiny little bit of
replication experience, ya know), but I can't imagine why it would
slow down searches, as it's not a field that anyone would be using
for a search.

You seem to be claiming something that I've never seen, that a SELECT
query on a replicated table will take longer to return the result
than on a non-replicated table? Is this what you're claiming? What I
see if I test it is performance penalty of approximately double, but
only if I do 1000 repetitions of the same thing.

I originally did this on a 4900-record table, and got an advantage of
2:1 for 1000 repetitions, but I've deleted the results. I then
appended records to bring the count up to 93K records and tested
again.

250 repetitions of opening a 93K-record table in a recordset, and
issuing a .MoveLast:
MOVELAST: (250)
Start tblPerson: 10/6/2008 8:37:27 PM
Finish tblPerson: 10/6/2008 8:38:05 PM
Elapsed tblPerson: 00:38
MOVELAST: (250)
Start tblPersonUnReplicated: 10/6/2008 8:38:05 PM
Finish tblPersonUnReplicated: 10/6/2008 8:38:12 PM
Elapsed tblPersonUnReplicated: 00:07

If I reverse the order, I get:
MOVELAST: (250)
Start tblPersonUnReplicated: 10/6/2008 8:40:13 PM
Finish tblPersonUnReplicated: 10/6/2008 8:40:21 PM
Elapsed tblPersonUnReplicated: 00:08
MOVELAST: (250)
Start tblPerson: 10/6/2008 8:40:21 PM
Finish tblPerson: 10/6/2008 8:41:02 PM
Elapsed tblPerson: 00:41

So, it does seem that for a MoveLast, the disadvantage of replication
becomes markedly greater the more records you have (though not
linearly, as 20X the records did not make it 20X as slow, it just
went from twice as slow to 4X as slow).

When testing .FindFirst, instead (which is well-known to be slow, of
course), the results are less clearcut. The unreplicated table is
still faster, but it's no longer a 2:1 advantage (using the 93K-
record table):

FINDFIRST: (100)
Start tblPerson: 10/6/2008 8:30:02 PM
Finish tblPerson: 10/6/2008 8:30:39 PM
Elapsed tblPerson: 00:37
FINDFIRST: (100)
Start tblPersonUnReplicated: 10/6/2008 8:30:39 PM
Finish tblPersonUnReplicated: 10/6/2008 8:31:04 PM
Elapsed tblPersonUnReplicated: 00:25

FINDFIRST: (100)
Start tblPersonUnReplicated: 10/6/2008 8:31:34 PM
Finish tblPersonUnReplicated: 10/6/2008 8:31:58 PM
Elapsed tblPersonUnReplicated: 00:24
FINDFIRST: (100)
Start tblPerson: 10/6/2008 8:31:58 PM
Finish tblPerson: 10/6/2008 8:32:36 PM
Elapsed tblPerson: 00:38

I'm not sure why that would be.

I ran another test, on a SELECT statement, using a loop that would
write a WHERE clause for 10 different values in one of the indexed
fields. Here are the results:

WHERE: (25)
Start tblPerson: 10/6/2008 8:56:37 PM
Finish tblPerson: 10/6/2008 8:57:14 PM
Elapsed tblPerson: 00:37
WHERE: (25)
Start tblPersonUnReplicated: 10/6/2008 8:57:14 PM
Finish tblPersonUnReplicated: 10/6/2008 8:57:41 PM
Elapsed tblPersonUnReplicated: 00:27

WHERE: (25)
Start tblPersonUnReplicated: 10/6/2008 8:58:49 PM
Finish tblPersonUnReplicated: 10/6/2008 8:59:15 PM
Elapsed tblPersonUnReplicated: 00:26
WHERE: (25)
Start tblPerson: 10/6/2008 8:59:16 PM
Finish tblPerson: 10/6/2008 8:59:52 PM
Elapsed tblPerson: 00:36

The differences here just don't seem that significant to me, except
on operations you're not going to be doing in a real application (how
often do you need to .MoveLast more than once at a time?).

 

[John Graham]

I tried that as an experiment.

I think that's ridiculous.

I tried it: you thought about it.

Post back again when you have tried it.: I'll be interested to hear what
it's like on a newer computer.

 

[^Simon^]

I think that's ridiculous.

I tried it: you thought about it.

Post back again when you have tried it.: I'll be interested to hear what
it's like on a newer computer.

 

[David W. Fenton]

[quoting me:]

I tried it: you thought about it.

Maybe you're incompetent.

Post back again when you have tried it.: I'll be interested to
hear what
it's like on a newer computer.

Maybe you just don't know WTF you're doing.