Accessing SQL tables in VBA for GP 1

[mach5]

There are so few VBA books out there and even fewer that deal with accessing databases of any kind, let alone the specifics of getting at SQL tables. Any help would be greatly appreciated as I really have no idea where to begin.

Somehow I need to be able to send "SELECT/FROM/etc" statements to the SQL server, but I don't know how to set up and/or access the ODBC connection in VBA. I think this is where I'm having difficulty, but I really have no idea.

Thanks in advance,

Mark Handford

 

[GaryWilsonCPA]

Is this an odbc issue. With access,crystal or many other products you use an odbc connection to get the great plains data. The data driver used to be called faircom. If the data is sql server then use the microsoft sql driver.

 

[mach5]

The ODBC connection is set up on my computer, I just don't know how to set it up in VBA. I've been reading a few VB books that say I need to do something along the lines of "ODBC;UID=blah blah;PASSWORD=somethingorother;" etc. I've attempted to do this a number of different ways to no effect.

 

[hooggie]

Before you can use jet direct to access the database in SQL you will need to switch on the MSDAO in the resources.

Go to Tools >> References and mark the box for the most current version of DAO that you have installed.

Then what you were trying sounds like it is going along the right lines.

Be warned that any writing the you do to the tables will totally screw up the database that you are working on and create an expensive nightmare to fix if you end up creating data corruption by side stepping the business logic.

For enquiries this is great. If you need any more info let me know.

 

[CarolineS]

Hooggie,

I'm working with mach5 on this project he's mentioned above.

Can you go into further detail about this:
>>Be warned that any writing the you do to the tables will totally screw up the database that you are working on and create an expensive nightmare to fix if you end up creating data corruption by side stepping the business logic<<

...........and tell us how best to handle the situation?
I was under the impression that creating a table on the SQL server would be the easiest way to create a simple lookup for, say, a button we're adding to a window in GP. Is there a way to partition that lookup elsewhere, so as not to come close to the sacrosanct GP data tables?

Thanks

 

[hooggie]

if you are creating your own tables and writing to SQL this is fine. I was cautioning against overriding what Great Plains does and changing what is in the Great Plains tables. There are some cases where this is fine but in other cases you can destroy the integrity of the system and make the auditability of the system worthless.

In short if you are creating your own tables or using great plains tables for enquiries you have nothing to worry about.

If you are writing to great plains tables you really need to be thorough to make sure that you are not underming the stability of the system. I really mean be thorough, even something that sounds innocent can have some enexpected results.

Good luck