×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Accessing SQL tables in VBA for GP

Accessing SQL tables in VBA for GP

Accessing SQL tables in VBA for GP

(OP)
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

RE: Accessing SQL tables in VBA for GP

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.  

RE: Accessing SQL tables in VBA for GP

(OP)
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.

RE: Accessing SQL tables in VBA for GP

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.

RE: Accessing SQL tables in VBA for GP

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

RE: Accessing SQL tables in VBA for GP

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close