VBA to get data from database.

[DHolcombe]

hi,

I am trying to use an excel vba program to get data from a database and then use this data in my program. But i have no clue about where to start.

Can someone help? I know this is kinda vague but i am not sure exactly what i need to know. Not in any of the books that i have on excel vba.

 

[DHolcombe]

Obiron,

Can you explain further this statment:

I am sure you can imagiane the major overhead of maintaining this sort of code in Excel. I would suggest that you try to get the sqlText stored in the database as a view and then simply write sqlText to "select * from myView" This moves support of the logic to the database where it is more useful because it can be re-used.

 

[DHolcombe]

Obiron,
Why did you say that there would be major overhead in maintaining the code.......wont the database stay the same and if it stays the same wont the query stay the same. I am making a few assumptions here but this is what i was thinking.

Also, what is a "view". Never heard of this before. Keep in mind i am a newbie to database stuff.

 

[xenou]

I would take his words in the sense that the more you can do on the "server side" the better the performance. In short, you can save your query on the database itself, and then simply "query the query". This, however, requires access to the database that you may or may not have - the ability to create views, for instance, or at least the cooperation of your dba to do it for you.

You're query might not require a lot of overhead - it all depends. Maybe the poor database is overtaxed already, or maybe it has plenty of resources, or maybe all this will change in the future. For now don't worry about it! I've written queries in vba that are not stored on the server and did not suffer headaches over bad performance or maintenance nightmares - it all depends on the size and scope of your project, and who knows how many other things.

ξ

 

[DHolcombe]

Thanks for your inputs!

 

[xenou]

No problem. You've picked it up very fast too - you must have a knack for this kind of thing.