Boss: So, currently, you have our information stored in Access.

GtDS: Yes.

Boss: And every day, you have a VBA script that refreshes our data.

GtDS: Yes.

Boss: How does it do that?

GtDS: Do we have doughnuts?

Boss: Please stay on topic in this imaginary, but potential, conversation.

GtDS: Sorry.

Boss: So, what does your script do?

GtDS: It removes existing data for the current quarter, and then re-imports updated information from the current quarter's CSV file.

Boss: Is that all?

GtDS: Please don't interrupt me.

Boss: Sorry. Doughnut?

GtDS: Thank you. Anyway, after the current quarter's data is re-imported..

Boss: Does "re-imported" need a hyphen?

GtDS. Dunno. Was wondering that myself. Anyway, after the current quarter's data is re-imported, or reimported, two queries are run. Each looks at the entire data set and does some SQL sorting and grouping, resulting in two smaller tables.

Boss: Which we then turn into PivotTables in Excel?

GtDS: Totally.

Boss: However, you've said that we're now hitting Access' 2GB limit, so you want to put everything into MySQL, instead?

GtDS: Right. Have a doughnut.

Boss: But you need to know what sort of tool can be used to pull off these tricks on a Windows box?

GtDS: Yes. In the past, I've used PHPMyAdmin to upload information and perform some queries. But I need to perform scripted actions, like that current-quarter bit we discussed just a few seconds ago.

Boss: You said that MattKime...

GtDS: How'd you know his name? He's some guy on the internet.

Boss: I'm that good. Anyway, Matt said that you can use the command line for such things.

GtDS: Yeah, but I don't know how that works for scripting. Can a script be saved as a text file? If so, what language is the script it? I need to know this sort of stuff before I can feel confident in recommending MySQL as a solution.

Boss: Have some cash.

GtDS: Thank you.

Boss: Want to knock off early?

GtDS: No, thanks. Strong work ethic and all that.

Boss: Okay. Just let me know about that scripting a MySQL database when you have a chance?

GtDS: You got it.

Perl.

Learn it. Live it. Love it.

- Shadow

Damn....I mean...what kind of queries are you running? I'd be hoping to do this all live from MySQL...

How strong are you with SQL? I've seen very few queries that can't be done in SQL....but maybe you have one.

I'm also curious who you work for. They have 2GB of data they need to toss around DAILY and they've been doing it in Access? OUCH!

MySQL can be scripted in any language that can talk with it. PHP, Perl, Ruby. However, if you want to avoid relying on other scripting languages, MySQL can be told to execute a text file.

Where does this CSV file come from?

this link should give you the info you're looking for -

[dev.mysql.com]

feel free to PM me if you have any questions.

---

How does your boss feel about Linux? If you're going to go MySQL, might as well dump the rest of Microsoft while you're at it and save some licensing dough (for more doughnuts of course). The hardest part that I've found about MySQL is that I've not found many good GUI front ends for it. The database it self is free and rock-solid, but unless you have a GUI of some type everything is command line SQL commands. Not a problem if you live and breath SQL...but I have a life to live and need sunlight and air and such...so try taking a look at Rekall Revealed (Linux only app last time I checked). It is an FOSS(free open source software) GUI for MySQL. There are others, but they cost money. Some offer trial periods and such. Data is data is data. It doesn't really matter if it is being coughed up by Access, MSSQL, MySQL, Oracle, or PostgreSQL. The hardest part is getting data OUT of Access and into something else. M$ makes it really easy to get stuff in, then they leave you with CSV or Excel files as the only option to get stuff out. There are programs out there that will suck the data out of Access and into MySQL creating tables and such that are even formatted correctly...but again, those cost money as best I can tell.

[www.rekallrevealed.org]

Update: it looks like there is a Windows Version of RekallRevealed available now.

---

**************************************
MacResource User Map: [www.zeemaps.com]#



Edited 2 time(s). Last edit at 03/30/2006 09:14AM by ztirffritz.

MySQL also works perfectly fine on Mac OS X. CocoaMySQL does the job for me whenever I need a GUI but I rely on it rarely.

Also, be very careful matching up your datatypes between Access & MySQL. Then again, this is always an issue when going between database systems.

---

Perl creeps me out.

MySQL queries are strange.

---

_______________________________________________________________
Security is just a word. The act of being secure is entirely different.
"...but I think that to make your dream a reality, having a dream itself is a mandatory prerequisite"
From A Book: Walking Through The Impossible

Just because it has two clutches, shifts faster, uses a torque converter or has near-infinite gear ratios, requires less driver intervention and is more efficient doesn't make it better in my opinion.
Save The Manual Transmission

Forget about MySQL and go straight to PostgreSQL (collect $200).
It has support for stored procedures and triggers AND is supported by all them other scripting languages.

The problem, though, is that stored procedures and triggers ain't mastered overnight, but that doesn't matter because there's all them scripting languages(!)

[www.postgresql.org]

Oh, almost forgot...PHPMySQL. Web hosted database administration. That works pretty well too.

---

**************************************
MacResource User Map: [www.zeemaps.com]#

shadow:
Perl - gotcha. I'm all set to learn how to do Perl conditionals and loops and @ and $ and like that. I already have the O'Reilly Perl Pocket Reference; what keywords should I investigate for connecting a Perl script to a MySQL database?

mattkime:
Well, I don't know if I'm supposed to say where I work, so I'll hint that it's a gigantic telecom company, based in (well, formerly based in) Overland Park, KS.

How strong am I at SQL? Um...not bad. Enough to be dangerous? More accurately, enough to look at the results of a complex query in Access and think, "I totally understand that, but boy, am I glad I don't have to type that out."

Access was my idea. This started off in Excel and MapInfo...Access was better. But then the project ballooned into 2GB, and there you go.

Each CSV file is a collection of data from electronic doodads in several states that apparently go "bleep" every few minutes. The CSV shows the bleeps, along with various identifiers. Access is used to sort/group/sum/count by identifier.

ztirffritz:
Thanks; I appreciate all the advice. In this one case, I wouldn't want to push for a different OS at this time. We already have a computer running MySQL, so I'm going to see if that's an option. I'm just trying to not spend time doing workarounds in Access, because that's just *not* the right tool for the job.

mattkime Wrote:
-------------------------------------------------------
> MySQL also works perfectly fine on Mac OS X.
> CocoaMySQL does the job for me whenever I need a
> GUI but I rely on it rarely.

Right. I'm playing around with everything on my PowerBook before making any real suggestions to The Boss.

microchip13 Wrote:
-------------------------------------------------------
> Perl creeps me out.
>
> MySQL queries are strange.

Fair enough.

Seacrest Wrote:
-------------------------------------------------------
> Forget about MySQL and go straight to PostgreSQL
> (collect $200).
> It has support for stored procedures and triggers
> AND is supported by all them other scripting
> languages.

I'm sure you're right, but I'm going to stick with crappy old MySQL (which I don't know how to use, yet, anyway) for now.

ztirffritz later Wrote:
-------------------------------------------------------
> Oh, almost forgot...PHPMySQL. Web hosted database
> administration. That works pretty well too.

Yeah, but what's with that filesize limit on imports from CSV!?

GtDS