Help creating Module with connection string.

[dkotula]

Hi,

I would like to create a module in a database that contains a ODBC connection string, and can be called on in a form using VB.
Here is what I'm using for a connection string:
ODBC;DSN=TakeStock;HOST=tstock;PORT=2600;DB=domdata;UID=My UserID;PWD=My Password
And I'm currently using a Query like this:
SELECT icWhseItem_0.ItemNum, icWhseItem_0.Description1, icWhseItem_0.Description2, icItem_0.MajorCat, icWhseItem_0.Loc, icWhseItem_0.OnHandQty, icItemDesc_0.ExtDesc, icWhseItem_0.Whse, icWhseItem_0.udChar5
FROM PUB.icItem icItem_0, PUB.icItemDesc icItemDesc_0, PUB.icWhseItem icWhseItem_0
WHERE icItem_0.CoNum = icItemDesc_0.CoNum AND icItem_0.CoNum = icWhseItem_0.CoNum AND icItem_0.Description1 = icWhseItem_0.Description1 AND icItem_0.ItemNum = icWhseItem_0.ItemNum AND icItem_0.ItemNum = icItemDesc_0.ItemNum AND icItemDesc_0.CoNum = icWhseItem_0.CoNum AND icItemDesc_0.ItemNum = icWhseItem_0.ItemNum

Could someone show me how this would be used in a module, that I could just call upon this information within a form.

Any imput would be greatly appreciated.
Thanks
Dave

 

[Kreszch68]

Hi,

if you use a system or user DSN, the code will look like

Dim oCn as New ADODB.Connection
Dim oRs as New ADODB.Recordset
 
oCn.Open "TakeStock"
 
With oRs
        .Open SQL_YourQuery, oCn, adopenDynamic, adLockOptimistic
        'do what you need to do with the recordset
        .Close
End With
 
oCn.Close

The query is constructed by the function:

Public Function SQL_YourQuery () as string
 
    SQL_YourQuery = "Here comes your SQL statement"
 
end Function

 

[Kreszch68]

And to use ADODB, you need to set a reference to

Microsoft ActiveX Data Objects 2.x Library

 

[dkotula]

I'm not sure how to set this all up, but we currently connect to a progress database using ODBC with a system DSN.
I'm assuming that the connection string needs applied somewhere, would that be applied to the module somewhere? Just wondering about where authentication and other connection information would be applied.
Please bear with me as I have never coonected any other way but through a pass-through query.
The data pulled from Progress only needs to be read only and save to a table, so data on the server doesn't need modifying, it is strictly lookup. This is to provide functions that our ERP doesn't provide.

Thanks for your response.
Dave

 

[xenou]

What you are describing sounds like a query to me, not a "module". You mention you have only connected before with a pass-through query - that is the right way to do it. The way to create a pass through query is described at this page under the heading "create a pass through query":
http://office.microsoft.com/en-us/a...y-using-a-pass-through-query-HA010206488.aspx

 

[dkotula]

I just thought maybe there was a way to have a common connection point containing tables that would be part of the database through a module, that could just be called on within a form to provide data. It sounds like I'll have to go the route I was using.

 

[dkotula]

Ok, revisited.
Here is what I have now:
Command Button on my a form.

Private Sub Command2_Click()
Dim oCn As New ADODB.Connection
Dim oRs As New ADODB.Recordset
 
oCn.Open "Takestock", "sysprogress", "sysprogress"
 
With oRs
       .Open ItemLookup, oCn, adopenDynamic, adLockOptimistic
       .Open Location, oCn, adopenDynamic, adLockOptimistic
       .Open POInfo, oCn, adopenDynamic, adLockOptimistic
        'do what you need to do with the recordset
' Find out if the attempt to connect worked.
   If oCn.State = adStateOpen Then
      MsgBox "Succesfully Connected to Take Stock!"
   Else
      MsgBox "Take Stock Connection Failed"
   End If
End With
 
oCn.Close
End Sub

and 3 modules I created
ItemLookup:

Public Function ItemLookup() As String
ItemLookup = "SELECT icWhseItem_0.ItemNum, icWhseItem_0.Description1, icWhseItem_0.Description2, icItem_0.MajorCat, icWhseItem_0.Loc, icWhseItem_0.OnHandQty, icItemDesc_0.ExtDesc, icWhseItem_0.Whse, icWhseItem_0.udChar5" & _
"FROM PUB.icItem icItem_0, PUB.icItemDesc icItemDesc_0, PUB.icWhseItem icWhseItem_0" & _
"WHERE icItem_0.CoNum = icItemDesc_0.CoNum And icItem_0.CoNum = icWhseItem_0.CoNum And icItem_0.Description1 = icWhseItem_0.Description1 And icItem_0.ItemNum = icWhseItem_0.ItemNum And icItem_0.ItemNum = icItemDesc_0.ItemNum And icItemDesc_0.CoNum = icWhseItem_0.CoNum And icItemDesc_0.ItemNum = icWhseItem_0.ItemNum"
End Function

Location:

Public Function Location() As String
Location = "SELECT icWhseItem_0.ItemNum, icWhseItem_0.Loc" & _
"FROM PUB.icWhseItem icWhseItem_0"
End Function

POInfo

Public Function POInfo() As String
POInfo = "SELECT poDocHdr_0.DocNum, poDocHdr_0.VendNum, poDocHdr_0.VendName" & _
"FROM PUB.poDocHdr poDocHdr_0" & _
"ORDER BY poDocHdr_0.DocNum DESC"
End Function

When I execute the command button, It shows a connection to the database, with .Open ItemLookup, .Open Location, and .OpenPOInfo remarked out.
I feel as though something is missing when it comes to running the modules.
Also, can I utilize all three modules within one command?

Thanks
Dave

 

[xenou]

When I execute the command button, It shows a connection to the database, with .Open ItemLookup, .Open Location, and .OpenPOInfo remarked out.

Huh? You see a connection with things marked out in it? No comprendo. You should see only your message, either "Succesfully Connected to Take Stock!" or "Take Stock Connection Failed"

Public Function POInfo() As String
POInfo = "SELECT poDocHdr_0.DocNum, poDocHdr_0.VendNum, poDocHdr_0.VendName" & _
"FROM PUB.poDocHdr poDocHdr_0" & _
"ORDER BY poDocHdr_0.DocNum DESC"
End Function

You don't have spaces between the lines.
You'll get (see how the FROM is mashed up against the line before it):
SELECT poDocHdr_0.DocNum, poDocHdr_0.VendNum, poDocHdr_0.VendNameFROM PUB.poDocHdr poDocHdr_0"
You have to view your SQL strings to make sure they come out right, before you use them - always do this when using a SQL string for the first time - debug.print it or msgbox it to look at how it comes out.

These aren't modules. They are functions.

 

[dkotula]

I was able to test my connection to the database excluding the code "with Ors" and the response I got bace was that it connected.

Where would I apply the functions at, within the vb code on my form? At the root of the form?

Sorry if I seem like a newbie, I haven't done any of this for quite some time.

 

[dkotula]

Here is my revisions,

Option Compare Database
Option Explicit

Public Function ItemLookup() As String
ItemLookup = "SELECT icWhseItem_0.ItemNum," & _
    "icWhseItem_0.Description1," & _
    "icWhseItem_0.Description2," & _
    "icItem_0.MajorCat," & _
    "icWhseItem_0.Loc," & _
    "icWhseItem_0.OnHandQty," & _
    "icItemDesc_0.ExtDesc," & _
    "icWhseItem_0.Whse," & _
    "icWhseItem_0.udChar5" & _
"FROM PUB.icItem icItem_0," & _
    "PUB.icItemDesc icItemDesc_0," & _
    "PUB.icWhseItem icWhseItem_0" & _
"WHERE icItem_0.CoNum = icItemDesc_0.CoNum" & _
    "And icItem_0.CoNum = icWhseItem_0.CoNum" & _
    "And icItem_0.Description1 = icWhseItem_0.Description1" & _
    "And icItem_0.ItemNum = icWhseItem_0.ItemNum" & _
    "And icItem_0.ItemNum = icItemDesc_0.ItemNum" & _
    "And icItemDesc_0.CoNum = icWhseItem_0.CoNum" & _
    "And icItemDesc_0.ItemNum = icWhseItem_0.ItemNum"
End Function

Public Function Location() As String
Location = "SELECT icWhseItem_0.ItemNum, icWhseItem_0.Loc" & _
"FROM PUB.icWhseItem icWhseItem_0"
End Function

Public Function POInfo() As String
POInfo = "SELECT poDocHdr_0.DocNum, poDocHdr_0.VendNum, poDocHdr_0.VendName" & _
"FROM PUB.poDocHdr poDocHdr_0" & _
"ORDER BY poDocHdr_0.DocNum DESC"
End Function

Private Sub Command2_Click()
Dim oCn As New ADODB.Connection
Dim oRs As New ADODB.Recordset
 
oCn.Open "Takestock", "sysprogress", "sysprogress"
 
With oRs
       .Open ItemLookup, oCn, adopenDynamic, adLockOptimistic
       .Open Location, oCn, adopenDynamic, adLockOptimistic
       .Open POInfo, oCn, adopenDynamic, adLockOptimistic
        'do what you need to do with the recordset
   ' Find out if the attempt to connect worked.
   If oCn.State = adStateOpen Then
      MsgBox "Succesfully Connected to Take Stock!"
   Else
      MsgBox "Take Stock Connection Failed"
   End If
End With
 
oCn.Close
End Sub

Looks like I have to do a lookup of the tables in the database to verify table names and fields, but I think I'm getting some where.

Just got an error in my SQL code in around [.icItem icItem_0, PUB.icItem]

I'm guessing I'm righting the function in the correct location now.