473,563 Members | 2,563 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Moving from mySQL to Access


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.
Nov 12 '05 #1
2 1889
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(tempora ry 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

Nov 12 '05 #2
Bob Quintal <bq******@gener ation.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(tempora ry 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
Nov 12 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
732
by: Creigh Shank | last post by:
On moving my data files from /var/lib to /mnt/ramdisk I seem to be missing something. I've changed every .ini and .conf file I can find (/etc/my.cnf, /etc/init.d/mysql, /etc/init.d/mysqld, /etc/php.ini and /etc/httpd/conf/httpd.conf (for DAV-lock)). MySQL seems to start ok, but when I use my application through Apache the error message is...
0
553
by: Jon Miller | last post by:
I've tried moving the database from one partition to another and now I'm = getting the following error: # Starting mysqld daemon with databases from /data/mysql 030820 23:48:08 mysqld ended When I issue #/ /usr/share/mysql/mysql.server start Anyone have any idea what's gone wrong. I changed the datadir in both = /etc/my.cnf and...
4
5489
by: Stephen Ghelerter | last post by:
I am moving a web site with a MySql database to another server. Can I create a database on the new server with the same name and then move the tables there, or is life not that simple? Or can I create tables with no data and then replace them with the files from the other server? I know I can export them as text files and them import them,...
3
4484
by: Rock2005 | last post by:
Hi All, I have a hard drive that fails to boot with some mysql 4.1 data I would like to keep. Have slaved that drive to another PC (with mysql 4.1.12 loaded) and have saved other data and am able to access its MYSQL folder ... I tried to copy the data folder into corresponding directory on new drive but when I try ot look at any of the...
2
1266
by: moller | last post by:
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...
9
7257
by: Daven Thrice | last post by:
If I have a fairly big Access MDB, that is relational, and has, say, 100 objects (forms, reports, modules, etc.), what is the path to get this database "online". Is there a way to put the tables online and then distribute the front end to each user? What if I want to have all the forms and stuff online? How do you approach something like...
34
5006
by: Karam Chand | last post by:
Hello I have been working with Access and MySQL for pretty long time. Very simple and able to perform their jobs. I dont need to start a flame anymore :) I have to work with PGSQL for my companies current project. I have been able to setup postgresql in my rh box and
3
2302
by: Thiagu.M | last post by:
Hi All i dont have much experience in databases or Mysql.i have my data in development m/c with Mysql 4.1.12, i have to move them to production m/c with Mysql 5.0.3 .when tried it showed error with displaying # mysql -u root mydatabase < backup1.sql ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that...
2
38928
by: fuzzybr80 | last post by:
I am using MySQL 5.0 with a number of innodb tables whose ibdata files are growing quite quickly and filling up the /var partition (file is /var/mysql/ibdata1). Earlier on I followed instructions in the docs to create a new ibdata file on a bigger partition /disk2/var/mysql/ibdata2) and set that to autoextend instead. However I would like...
0
7579
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
8101
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7630
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
7941
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
3628
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3612
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2077
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1194
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
909
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.