doing the right thing

[missprogrammer]

I run this report weekly and I want to increase the processing time. I dont know if the way I did it the first time was the best way, I created a connection to a SQL Database using Access(ado). Would it have been better for me to use VBA using a sql to the sql tables... by doing so would this speed up my processing time.. I'm probably not making my self clear,,,,but thanks in advance.

 

[MichaelRed]

Unfortunatly have to agree on the clarity of the issue. Perhaps you could take a little bit of your time to think your way through the issue(s).

In general, DAO is faster than DAO, but there are situations where this is not true. ADO is ALWAYS an ODBC 'wrapper', so if the db is accessed via ODBC, then ADO may be a better choice. If the db is in Ms. Access (Jet/MDB file) then IF you insist on using Excel to do the work, DAO is probably better, but you would probably need to LINK the tables to MS. Access - which, in turn, probably means an ODBC connection. So, in summary we could go 'round, and 'round, and 'round ...


MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[missprogrammer]

well, what i did the first time is probably stupid, and the more i think about it I wonder why I approached the situation like that.. But, I am learning... I have the db already in SQL an odbc conn. and i linked the tables in access(which is what i did the first time). But anyway the report I am creating must be created in Excel,why ,that's what the clients want...Tell me I can still do the same thing I did using access using odbc conn to sql db. and where can I find some documentation on how to get started....


And Micheal Thank you

 

[MichaelRed]

Hmmmmmmmmmmm,

ONE way (although not my favorite) would be to embed an excel spreadsheet in your access db, link the embedded spreadsheet to a MS Access query. Edit the Spreadsheet in situ to obtain the "report" you want and just save it. A bit weird to get it set up properly, but when complete the Data in Access will always 'feed' the report in Excel and you should be able to open it (the UUUUUGGGGGGGGHHHHHHHLLYYYYYY spreadheet thingggyyyyy) as an Excel doc or as the embeded thing in Ms. Access. You can have it Your way and the (vapid) client can have it their way.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[jonscott8]

PMFJI, but ADO is NOT ALWAYS an ODBC 'wrapper'. If the back-end data source offers an OLE DB provider (as SQLS and Jet do), ODBC can be bypassed altogether. Jon Hawkins

 

[MichaelRed]

JOn,

You may be correct. I only know what (little) I've read and the even less experience. It sure SEEMS like ODBC -in ever instance I've used it (you know the speed of molassas in Jan At the N. Pole thinnggyyy), but the TRUE underlying maze ot possabilities could be somewhat different. I DID read that even the connection to currentproject was ODBC (in a thin disguise).

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[missprogrammer]

okay, quys you to have lost me...So what exactly is your conclusion....

 

[MichaelRed]

I do not think any approach other than my initial 'embed Excel' has been offered. If there was one, I miissed it.

So, there is no "conclusion". Just the single suggestion for an approach.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over