Macs and DB engines

[starl]

Do Macs have the db engine that the windows os has??? Is there a way I can do an Excel application that retrieves info from a db????

[shades]

In Microsoft's infinite wisdom (and monopoly), it does not include ODBC drivers for Excel for the Mac. There are some available, but you have to pay for them!!

Here is a link for OS X ODBC drivers ($29.95)

http://www.actualtechnologies.com/

[starl]

so... if they don't have access, there's no way to create tables? and have excel access them?

[shades]

so... if they don't have access, there's no way to create tables? and have excel access them?

Do you mean "if they don't have MS Access" or "If they don't have access to the databases"? Without the drivers, I don't think so.

One possibility, which includes an extra step, is if they have FileMaker Pro, they should be able to access the database through ODBC, and then Excel 2004 has the ability to import FileMaker files.

[starl]

no, I don't think that'll work.. we'll just have to do the entire thing in Excel.. *yuck*

thanks, tho.
btw - we need to get together and go over that project....

[shades]

After further investigation, you can access a database through AppleScript. I am just beginning to learn it with OS X. I had dabbled with it a little in OS 8, but that was years ago, and it has changed, improved, etc. so it's like I am starting over.

I will see if I can find someone who may have a script already written to accomplish this.

Would the access be through a web server or over a network?

What type of database?

[starl]

We're looking at an excel front end with data being stored in a db. It would be single users.

type of db... uhm - I don't know?

[shades]

You are pushing my knowledge envelope!!! Which is a good thing. I also have found that some can be done with Perl, but now this is moving far outside my realm of knowledge:

http://www.iodbc.org/

or here with the instructions:

http://www.iodbc.org/index.php?page=languages/perl/odbc-perlHOWTO

[shades]

Here is another web site that has ODBC drivers for several databases.

http://www.openlinksw.com/

[starl]

the solution has to be free. The package is for a bunch of teachers and they don't want to buy anything else

[shades]

Just a note: is that file you sent for Excel 2001 or Excel 2004?

The reason I ask, it will depend on which computer I hookup. No, they are not networked.:roll: And I only have a dial-up connection at home.

[starl]

It's Excel 97/2000/xp

the client has the latest excel on the mac

[shades]

Okay, I opened it, unzipped it, and ran it twice with Excel 2004 on OS X 10.3.4. Not a problem. Hitting the export button, exported all 62 columns, and the last sequence put the titles in bold in Word. Whole process took <10 sec. (I have 1.25 GHz processor with 512 MB RAM.)

[starl]

hmm - you mentioned another system.. can you try it on that?

[starl]

Here's version info from the problematic client:

There was a system update for Macs. I'm running 10.3.4 now. Maybe
that's it.. Also, I'm on XL version X

[shades]

Here's version info from the problematic client:

There was a system update for Macs. I'm running 10.3.4 now. Maybe
that's it.. Also, I'm on XL version X

Okay, that last part might be the clue. There are two Excel versions for OS X. The earlier version for OS X was called Excel v. X. I have read that it occasionally had problems with some cross-platform work. I had never upgraded to it, but went directly from 2001 to 2004.

I will check a few sites and try to determine whether that is a problem.

[shades]

Do you want me to try it with Excel 2001 on OS 9.2.2?

[shades]

Okay, I used Excel 2001 and Word 2001 on OS 9.2.2. While it was slower (300 MHz processor 384 MB RAM), it worked exactly the same way. Title was bolded, etc.

[starl]

hmm - now I get notifs.. weird.
anyway.. so you didn't see *any* problems....
hmm - duplicate the data until it takes up a couple thousand rows... I had cut short what I sent you (it's a big file).. see if that makes a difference. It shouldn't but..

[shades]

Okay, with Excel 2001 (OS 9.2.2) I tried it with 704 rows of data and seven columns selected, and it took about 3 1/2 minutes.

With Excel 2004 I had 5130 rows and seven columns selected, and it took 5 minutes just to get the first 400 rows into Word. It must be hogging the memory somehow, or not releasing. I can watch them being placed into Word and it is slower than I would expect. I am letting it still run, until Excel give sup, or my computer dies! ;)

Also, I tried on both systems to exit to see what would happen, and they both responded appropriately, that is, whatever was imported to the point was then finished in Word with proper formatting.

One thing I noticed, is that with so many rows, they should turn off Spelling and Grammar Checking in Word. IT tries to keep up, and then will flash a warning that these exceed the limit of Word to check (real time).

[starl]

it *is* a slow program.. tho seems slower on yours then mine.
else.. don't know what to say. I'll tell the client to try the updated version and give me their specs.

[shades]

It seems that the size of the file is the limiting factor. It's been 22 minutes and is only up to 850 rows on Excel 2004.

I will try to limit the rows to about 2000 and see what happens.

[shades]

Also, I noticed that when I tried to Exit, if there were few files, the Exit worked as expected. But I finally pulled the plug on the Excel 2004 after 25 minutes, but the Exit button didn't stop it. After 4 times of trying to stop it, I had to Force Quit Excel.

Now I am trying it with 2500 rows. In 8 minutes it has exported 350 rows. So a little faster.

If you don't mind I might look at the code tomorrow night, just to take a gander at what is going on.

[shades]

There must be something affecting the OS X version. Here are the results from 2500 rows of data, with seven columns selected:

Excel 2001 (OS 9.2.2) - 15 minutes completed and properly formatted in Word 2001

Excel 2004 (OS X 10.3.4) - at 35 minutes but only 44% complete

Thus a version running on a platform whose processor is 4 times slower about 40% less RAM out performs the faster processor by a wide, wide margin.

Now I am curious about the code itself. I don't have time tonight, but will examine it.

[shades]

Final results (both using 2500 rows - identical):

Excel 2001 (OS 9.2.2) - 15 minutes completed and properly formatted in Word 2001

Excel 2004 (OS X 10.3.4) - at 1 hour and 22 minutes properly formatted in Word 2004

Thus a version running on a platform whose processor is 4 times slower about 40% less RAM out performs the faster processor by a wide, wide margin (5 times faster on the slower machine).

[starl]

strange...
hmm - I did think the client once mentioned it locking up.. but it's not really.... Now - is it Excel or the OS???

[shades]

A Microsoft statement regarding ODBC drivers:

<http://www.microsoft.com/mac/products/excel2004/using.aspx?pid=usingexcel20
04&type=howto&article=/mac/LIBRARY/how_to_articles/office2004/xl_ODBC.xml>

[starl]

nope - bad link
hhm - I thought I had posted another message.. specs from the client....

Mac
10.3.4

Office

X

I edited the list down the the first four items on the list and hit
'Export'. That was 19 minutes ago and I'm still waiting.

[shades]

nope - bad link
hhm - I thought I had posted another message.. specs from the client....

Mac
10.3.4

Office

X

You did - post # 15.


I edited the list down the the first four items on the list and hit
'Export'. That was 19 minutes ago and I'm still waiting.
Something else must be going on. I know that with 20 items, it was done in less than 5 sec. Even on the older machine with 2001 it was still less than 10 sec.

I had other projects in the fire over the weekend - mostly theology, so I'll look again tonight at this one.

[shades]

nope - bad link
hhm - I thought I had posted another message.. specs from the client....

Mac
10.3.4

Office

X

I edited the list down the the first four items on the list and hit
'Export'. That was 19 minutes ago and I'm still waiting.

Okay, I pared the list in Excel down to 1,000 rows with 7 columns of data. It took exactly 7 minutes to do it - this with OS X 10.3.4 and Excel 2004.

Next I will try with 1,000 rows and 62 columns of data.

[shades]

Interesting: this time with 1,000 rows and selecting all 62 columns - 5 min. 42 sec. I assume that is because it doesn't have to filter any of the columns.

One question - once the client begins the process do they then work on other tasks in other programs? (Safari, Entourage, etc.) The reason I ask is that changing programs in the midst of it might slow down the process considerably. In fact, the other night I used one small program, switching out of Word, and the time was excessively long.

I will try the 25000 rows now with 62 columns. Then I will try it with activating other programs than Word to see what happens.

BTW, each time I run this I use a new copy of the original sheet, so that they all are "starting from the same point".

[starl]

but - the program works - just slow. ON this client, it ain't working at all!

[shades]

Okay, with 2,500 rows and 62 columns - it took 15 min. 58 sec. But of that time 1 min. 28 sec. was spent on the formatting at the end. (again OS X 10.3.4 and Excel 2004.)

I'm beginning to think that if there is any attempt to switch programs - of any kind, even back and forth between Word and Excel it starts to slow down.

With the client's computer, does the program freeze? Are they able to follow the percent window?

Are they going to upgrade from Excel v.X to Excel 2004? That might make a difference.

Sorry, I'm not more help on this.

[starl]

don't know. problem - my contact with the client having the problem is 3rd person.. *sigh* but - I'll take your latest resutls and run for now.
hey - didn't you say that you read something about conflicts? cross-app?

[shades]

One possibility here about conflicts:

Did they have a Test Drive version of Excel v.X on the machine, and then just installed the registered version, without uninstalling the Test Drive version? In some cases that can cause problems.


Special Bulletin: Before you install Microsoft Office v. X or the standalone versions (Microsoft Word X, Entourage X, Excel X, and PowerPoint X), you must first remove the Office v. X Test Drive from your computer. Please see Remove the Office v. X Test Drive before installing Office v. X or standalone versions of Word X, Entourage X, Excel X, and PowerPoint X.
http://www.microsoft.com/mac/support.aspx?pid=bulletin

[shades]

Here is a page that has some references for VBA programming in Excel v. X.

http://support.microsoft.com/default.aspx?scid=kb;en-us;310330

Also, I happened to notice a reference to MsgBox differences (basically the Help button does not show up in Excel v. X, but will in 97 and 2000). I wonder if there might be something connected with the MsgBox that is used that is causing a conflict.

[shades]

This may be it?


Running Macros

Macros that make calls to custom 16-bit DLLs The DLL source code must be recompiled into a PowerPC version before the macros will run in Excel X.

http://www.microsoft.com/mac/resources/resources.aspx?pid=resourcekits&rk=officex&article=/mac/officex/ork/File_Formats_Excel.xml