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]

This wrong also:

With oRs
       .Open ItemLookup, oCn, adopenDynamic, adLockOptimistic
       [COLOR=red].Open Location, oCn, adopenDynamic, adLockOptimistic[/COLOR]
[COLOR=red]       .Open POInfo, oCn, adopenDynamic, adLockOptimistic[/COLOR]

You can't set the record set to be three record sets at the same time. If you want three record sets present at the same time, you'll need to declare three objects to hold the recordset, like

Dim oRsItemLookup New ADODB.Recordset
Dim oRsLocation New ADODB.Recordset
Dim oRsPOInfo New ADODB.Recordset

If you don't need them to be present simultaniously, first close the object before opening it again. All depents on what you're about to do with the records.

 

[dkotula]

I think it is missing the "As" Do you mean:

Dim oRsItemLookup As New ADODB.Recordset
Dim oRsLocation As New ADODB.Recordset
Dim oRsPOInfo As New ADODB.Recordset

If I enter this, how do I call the function?
Do I just ditch the code below?

With oRs
       .Open ItemLookup, oCn, adopenDynamic, adLockOptimistic
       .Open Location, oCn, adopenDynamic, adLockOptimistic
       .Open POInfo, oCn, adopenDynamic, adLockOptimistic

You are correct, I do want to reference all three functions, but I may be able to do this individually. In a form, an entry will be entered into a textbox, and other textboxes will be populated based on that entry. I need to reference 3 different tables from the database to completely get the desired information. ItemNum, PONum, and location would be the input, then comes the results that will be saved to a table with some additional manual entry.

Thanks for your reply
Dave

 

[dkotula]

Can I combine all of the SQL code in the function?

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

 

[Kreszch68]

Declare:

Dim oRsItemLookup As New ADODB.Recordset
Dim oRsLocation As New ADODB.Recordset
Dim oRsPOInfo As New ADODB.Recordset

OpenRecordSets:

oRsItemLookup.Open ItemLookup, oCn, adopenDynamic, adLockOptimistic
oRsLocation.Open Location, oCn, adopenDynamic, adLockOptimistic
oRsPOInfo.Open POInfo, oCn, adopenDynamic, adLockOptimistic

Maybe it's better to begin with one query and pick the less complicated one, that will make it a lot easier to debug.
If you have one sql statement working you'll know the connection etc is ok.

 

[xenou]

You must put spaces between words in SQL.

Incorrect:

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

Correct:

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

See the difference?

ξ

Maybe it's better to begin with one query and pick the less complicated one, that will make it a lot easier to debug.
If you have one sql statement working you'll know the connection etc is ok.

Good advice. Always start with a simple query first - make sure it works.

 

[dkotula]

Xenou,

Thank you for the response, I have been looking for the cause of my errors, and you hit it on the head with inserting spaces.

I'm still working with the SQL statements, and ran in to another error in this line according to the debugger:

       .Open ItemLookup, oCn, adopenDynamic, adLockOptimistic

The Error I recieve is: [DataDirect][ODBC OPENEDGE]Column not found/specified (7520)
I'm sure this has to do with column from the table in the Progress Database, and will proceed to look into that.

Thanks again
Dave

 

[xenou]

If its possible to connect in another way to run your query that's always helpful ... for instance, if you have a way to run queries manually through graphical interface. It's quite a pain having to debug sql that you can only run in code - but if that's necessary, it is a good idea to debug.print your query text so you can see what it *really* says.