Moving from mySQL to Access

[moller]

Im looking in to the possibility of moving from mySQL to
an access database.

My reasons are:
(1) Database is single user.
(2) Database local on users PC.
(3) Database has only 8 tables where 4 are filled at database creation
with aprox 20 rows each and are never added to after that.
(4) Database grows with ca 6000 rows/week.
(5) No data is ever deleted, exept... (6)
(6) After 18 months the data can be purged.
(7) No need to install a database. (My users have MS OfficePro)
So now I have a few questions:

(A) The largest table will have about 250000 rows after 18 month.
Can access handle that?

(B) The data is numbers, times and dates.
I only have data for 13 weeks so far but my test access
database grows with about 900kb/week and is now 10Mb.
That is about twice the size of the mySQL database, is that
about "par" or do I need to tweak the tables/datatypes?
(C) Inserting is a bit slow. Not crippeling just annoying.

220 rows inserted into static.
3080 rows inserted into o14.
1052 rows inserted into o14q.
1210 rows inserted into o15.

This took 31 seconds and on mySQL i hardly notice that it
takes time. Is this normal or should I blame table design
or the perl ODBC interface?
(D) Does anyone know of any good resources for perl/odbc/access/VB/COM/OLE.
(E) Vhat needs to be done by hand and what can be automated with COM/OLE?
I prefer perl but Visual Basic is acceptable?
(F) What do you use instead of decimals?
I'm using number in my test database.

(G) When testing I inserted data into the database while it was open in
Microsoft Access. When I had was done inserting data it was 70Mb
instead of the normal 9.5Mb. I't reproducible. What's with that?

(H) And while I have your attention. A question I should find the answer
for myself but since it's late and I'm tired I'll ask it anyway.

I have worked with MS Access for about 10 hours now but only using perl
and ODBC. Is there somwhere in Access where I can type an sql-query and
execute it?
Thanks in advance for any thoughts and answers.

[Bob Quintal]

mo****@notvalid.se wrote in news:uu***********@notvalid.se:

Im looking in to the possibility of moving from mySQL to
an access database.

My reasons are:
(1) Database is single user.
(2) Database local on users PC.
(3) Database has only 8 tables where 4 are filled at database
creation
with aprox 20 rows each and are never added to after that.
(4) Database grows with ca 6000 rows/week.
(5) No data is ever deleted, exept... (6)
(6) After 18 months the data can be purged.
(7) No need to install a database. (My users have MS
OfficePro)
So now I have a few questions:

(A) The largest table will have about 250000 rows after 18
month.
Can access handle that?
Access will hold 1GB of data+code. (2GB in more recent versions)
250K rows would allow 400 bytes per row.


(B) The data is numbers, times and dates.
I only have data for 13 weeks so far but my test access
database grows with about 900kb/week and is now 10Mb.
That is about twice the size of the mySQL database, is
that about "par" or do I need to tweak the
tables/datatypes?
Access sometimes bloats for obscure reasons(temporary tables).
You need to run tools->database utilities->compact database to
free up space.


(C) Inserting is a bit slow. Not crippeling just annoying.

220 rows inserted into static.
3080 rows inserted into o14.
1052 rows inserted into o14q.
1210 rows inserted into o15.

This took 31 seconds and on mySQL i hardly notice that it
takes time. Is this normal or should I blame table design
or the perl ODBC interface?
If access is updating indices and validating constraints, This is
sorta normal. Note that mysql and sql-server do this
asynchronously, make them appear faster.
(D) Does anyone know of any good resources for
perl/odbc/access/VB/COM/OLE.
sorry, I program in VB only.
(E) Vhat needs to be done by hand and what can be automated
with COM/OLE?
I prefer perl but Visual Basic is acceptable?
(F) What do you use instead of decimals?
I'm using number in my test database.

(G) When testing I inserted data into the database while it
was open in
Microsoft Access. When I had was done inserting data it
was 70Mb instead of the normal 9.5Mb. I't reproducible.
What's with that?
See above re: compacting the database.
(H) And while I have your attention. A question I should find
the answer
for myself but since it's late and I'm tired I'll ask it
anyway.

I have worked with MS Access for about 10 hours now but
only using perl and ODBC. Is there somwhere in Access
where I can type an sql-query and execute it?
select the query portion of the database objects window. open a
new query. The query design toolbar allows you to select between
the qbe grid and a simple SQL editor.


Thanks in advance for any thoughts and answers.

Bob Q

[moller]

Bob Quintal <bq******@generation.net> writes:

mo****@notvalid.se wrote in news:uu***********@notvalid.se:

Im looking in to the possibility of moving from mySQL to
an access database.

My reasons are:
(1) Database is single user.
(2) Database local on users PC.
(3) Database has only 8 tables where 4 are filled at database
creation
with aprox 20 rows each and are never added to after that.
(4) Database grows with ca 6000 rows/week.
(5) No data is ever deleted, exept... (6)
(6) After 18 months the data can be purged.
(7) No need to install a database. (My users have MS
OfficePro)
So now I have a few questions:

(A) The largest table will have about 250000 rows after 18
month.
Can access handle that?

Access will hold 1GB of data+code. (2GB in more recent versions)
250K rows would allow 400 bytes per row.

(B) The data is numbers, times and dates.
I only have data for 13 weeks so far but my test access
database grows with about 900kb/week and is now 10Mb.
That is about twice the size of the mySQL database, is
that about "par" or do I need to tweak the
tables/datatypes?

Access sometimes bloats for obscure reasons(temporary tables).
You need to run tools->database utilities->compact database to
free up space.

(C) Inserting is a bit slow. Not crippeling just annoying.

220 rows inserted into static.
3080 rows inserted into o14.
1052 rows inserted into o14q.
1210 rows inserted into o15.

This took 31 seconds and on mySQL i hardly notice that it
takes time. Is this normal or should I blame table design
or the perl ODBC interface?

If access is updating indices and validating constraints, This is
sorta normal. Note that mysql and sql-server do this
asynchronously, make them appear faster.

(D) Does anyone know of any good resources for
perl/odbc/access/VB/COM/OLE.

sorry, I program in VB only.

(E) Vhat needs to be done by hand and what can be automated
with COM/OLE?
I prefer perl but Visual Basic is acceptable?
(F) What do you use instead of decimals?
I'm using number in my test database.

(G) When testing I inserted data into the database while it
was open in
Microsoft Access. When I had was done inserting data it
was 70Mb instead of the normal 9.5Mb. I't reproducible.
What's with that?

See above re: compacting the database.

(H) And while I have your attention. A question I should find
the answer
for myself but since it's late and I'm tired I'll ask it
anyway.

I have worked with MS Access for about 10 hours now but
only using perl and ODBC. Is there somwhere in Access
where I can type an sql-query and execute it?

select the query portion of the database objects window. open a
new query. The query design toolbar allows you to select between
the qbe grid and a simple SQL editor.

Thanks in advance for any thoughts and answers.

Bob Q

Thanks.
Enough answers that I'll invest some more time into it.

M