Reposted - How to create a connection for SQL database

[Mac]

I want to find a part number in a sql table and if it is there, I want to
display quanity on hand.

I wan to do it in VBA.

I would appreciate if someone please show me how create connection etc.

 

[Tim Ferguson]

I want to find a part number in a sql table and if it is there,
I want to display quanity on hand.

I want to do it in VBA.

I would appreciate if someone please show me how create connection etc.

This is lifted straight from the help files -- have you looked? I've
added some comment lines here and there and shortened the procedure:


Dim cnn1 As ADODB.Connection
Dim rst As ADODB.Recordset
Dim strSQL As String

' Open connection.
strCnn = "Provider=sqloledb;" & _
"Data Source=srv;Initial Catalog=Pubs;User Id=sa;Password=; "
Set cnn1 = New ADODB.Connection
cnn1.Open strCnn

' Get the data you want
strSQL = "SELECT QtyOnHand" & vbNewLine & _
"FROM Inventory" & vbNewLine & _
"WHERE PartNumber = 10294"

' Open the query: you'll have to check the help file
' for the details of the types of cursor and lock you
' need for your situation
Set rst = New ADODB.Recordset
rst.CursorType = adOpenKeyset
rst.LockType = adLockOptimistic
rst.Open strSQL, cnn1, , , adCmdText

' output routine. You may want to trap the error if there
' is no record returned (i.e. non existent partnumber)
If rst.BOF Then
Debug.Print "We don't have any, it seems"

Else
Debug.Print "There are " & rst!QtyOnHand & _
" of them on hand."

End If

' release objects on the server
rst.Close
cnn1.Close


You are not likely to get a specific response unless you ask a specific
question. What exactly do you need help with?

You are also more likely to get help with an ADO question in an ADO group
-- the DAO in the name of this group kind of gives the game away!

B Wishes


Tim F

 

[Eric Schittlipz]

This is lifted straight from the help files -- have you looked? I've
added some comment lines here and there and shortened the procedure:


Dim cnn1 As ADODB.Connection
Dim rst As ADODB.Recordset
Dim strSQL As String

' Open connection.
strCnn = "Provider=sqloledb;" & _
"Data Source=srv;Initial Catalog=Pubs;User Id=sa;Password=; "
Set cnn1 = New ADODB.Connection
cnn1.Open strCnn

' Get the data you want
strSQL = "SELECT QtyOnHand" & vbNewLine & _
"FROM Inventory" & vbNewLine & _
"WHERE PartNumber = 10294"

' Open the query: you'll have to check the help file
' for the details of the types of cursor and lock you
' need for your situation
Set rst = New ADODB.Recordset
rst.CursorType = adOpenKeyset
rst.LockType = adLockOptimistic
rst.Open strSQL, cnn1, , , adCmdText

' output routine. You may want to trap the error if there
' is no record returned (i.e. non existent partnumber)
If rst.BOF Then
Debug.Print "We don't have any, it seems"

Else
Debug.Print "There are " & rst!QtyOnHand & _
" of them on hand."

End If

' release objects on the server
rst.Close
cnn1.Close


You are not likely to get a specific response unless you ask a specific
question. What exactly do you need help with?

You are also more likely to get help with an ADO question in an ADO group
-- the DAO in the name of this group kind of gives the game away!

B Wishes


Tim F

I would guess that the data might not be as easy to extract as:
SELECT QtyOnHand...
Perhaps you would need to add up the results of various stock transactions
to calculate the quantity on hand. But without any idea of table structure,
who can say? I sometimes wonder what standard of answer these people
expect.

 

[Tim Ferguson]

But without any idea of table structure,
who can say? I sometimes wonder what standard of answer these people
expect.

It's quite easy if you have the Windows XP Telepathic Extensions..

Tim F