Solved: Use a function to access DB

[JimOfRose]

:banghead:
Hello all. This is my first post and I really could use some help. I have a need to build an Excel Macro/function that can access a SQL DB. I've scoured the web looking for this asnwer and have begun to believe that perhaps I'm just missing some information about how this kind of thing should be done.

Here is what I would like to see happen:

Suppose in C2 there is a serial number for a given product. In C3 I would like to see the product number of that serial number. I would like to do this by pinging a DB with the contents of C2 and displaying the result.

So the fn would look something like:


Function pingSQL(ByVal cellContent AsString) As String
Dim rtrnVal As String





'connect to DB









'run query... something like "SELECT productNum from myTable where serialNum =" & cellContent









'result from query into = rtrnVal




Return rtrnVal
EndFunction

Am I smokin crack?

I would like to call this function inside of C3 (or any cell).

I already have an account set up on the SQL server to facilitate this. So username and password sensitivity is not an issue since I'll handle security from the server. I will be passing this spreadsheet to others inside our firewall.

Can someone please direct me to the right place that can explain how to do this? or else tell me I'm smokin something real good!

Thanks!

[mohanvijay]

Try ADODB.Recordset


Dim RS As ADODB.Recordset
Set RS = New ADODB.Recordset
Dim Con_Str As String
Dim QQry As String
Con_Str = "" 'Set connection string for help see here http://www.connectionstrings.com/
QQry = "" 'Set your query here

RS.Open QQry, Con_Str, adOpenStatic, adLockReadOnly
Range("a1").CopyFromRecordset RS

RS.Close
Set RS = Nothing

[JimOfRose]

Thanks mohan,
I think I'm close. I took what you gave me and was able to make the following:
Function CONV_SN(SerialNumber, SerialNumberOrigination) 'fn used to find info from a db regarding the serial number "SerialNumber"
Dim sqlstring As String
Dim rst As ADODB.Recordset 'I got an error here "user defined type not defined"
Set rst = New ADODB.Recordset
Dim QQry As String

conStr = "Provider=sqloledb;Data Source=myserver.net;Initial Catalog=SerialNumberConversion;User ID=SNReadOnly;Password=MyPassword123;"

Select Case (LCase(SerialNumberOrigination))
Case "fo"
Case "fooo"
SerialNumberOrigination = "foo"
break
Case Else
SerialNumberOrigination = "foobar"
break
End Select

If SerialNumberOrigination = "foo" Then
sqlstring = "select foobarSerNum from dbo.SerialNumberConvTable where foo ='" & SerialNumber & "'"
End If
If SerialNumberOrigination = "3Com" Then
sqlstring = "select fooSerNum from dbo.SerialNumberConvTable where foobarSerNum ='" & SerialNumber & "'"
End If

rst.Open sqlstring, conStr, adOpenStatic, adLockReadOnly

CONV_SN.CopyFromRecordset rst

rst.Close
End Function
But no matter how I try it, it still throws all kinds of errors. One line I'm def not sure of is "CONV_SN.CopyFromRecordset rst". I think this line throws the result of the query back into the function definition... kinda like "CONV_SN = result". But I could be wrong.

Can you spot anything I'm missing?

Thanks so much.

[Kenneth Hobs]

Obviously you would need to set the reference for that object in Tools > References > Microsoft ActiveX Data Objects 2.x Library

[JimOfRose]

After banging my head a bit more I finally learned a bit about how the VBA IDE works when debugging. I found a huge error in my code. Apparently DBA doesn't have a decent "Switch/Case" and doesn't like "Break". So I replaced all that with "If/Then/Else".

Now I have the following code:
Function CONV_SN(SerialNumber, SerialNumberOrigination)
On Error GoTo ErrorHandler

Dim Con_Str As String
Dim QQry As String
Dim typeDef As String

Set RS = CreateObject("ADODB.Recordset")

Con_Str = "Provider=sqloledb;Data Source=MySQLServer.net;Initial Catalog=SerialNumberConversion;User ID=SNReadOnly;Password=MyPassword123;"

If LCase(SerialNumberOrigination) = "fooo" Or LCase(SerialNumberOrigination) = "fo" Then
typeDef = "foo"
Else
typeDef = "bar"
End If

If typeDef = "foo" Then
QQry = "select foo from dbo.SerialNumberConvTable where bar ='" & SerialNumber & "'"
End If
If typeDef = "bar" Then
QQry = "select bar from dbo.SerialNumberConvTable where foo ='" & SerialNumber & "'"
End If

RS.Open QQry, Con_Str, adOpenStatic, adLockReadOnly

Range("a1").CopyFromRecordset RS

RS.Close
Set RS = Nothing

ErrorHandler:
MsgBox "An error occured - error " & Err.Number & ": " & Err.Description & ": " & Err.Source

End Function


And now that I have the ErrorHandler going on I now get the following error:

An error occured - error 3001: Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.: ADODB.Recordset

So, it looks like there must be a type mismatch here.

[JimOfRose]

Hey thanks mostly to mohan... I was able to find a solution elsewhere. Below is my working code:

Function CONV_SN(SerialNumber, SerialNumberOrigination)
Dim Con_Str As String
Dim QQry As String
Dim typeDef As String
Dim fieldSwitch

Set RS = CreateObject("ADODB.Recordset")

Con_Str = "Provider=sqloledb;Data Source=MySQLServer.net;Initial Catalog=SerialNumberConversion;User ID=SNReadOnly;Password=MyPassword123;"

If LCase(SerialNumberOrigination) = "fooo" Or LCase(SerialNumberOrigination) = "fo" Then
typeDef = "foo"
Else
typeDef = "bar"
End If

If typeDef = "foo" Then
QQry = "select bar from dbo.SerialNumberConvTable where foo ='" & SerialNumber & "'"
fieldSwitch = "bar"
End If
If typeDef = "bar" Then
QQry = "select foo from dbo.SerialNumberConvTable where bar ='" & SerialNumber & "'"
fieldSwitch = "foo"
End If

RS.Open QQry, Con_Str

CONV_SN = RS.Fields(fieldSwitch) 'Setting the RS.Fields with a var switched above was the key

RS.Close
Set RS = Nothing
End Function

This function return data from a DB to a cell according to your query. I'm not sure what would happen if more than one result was returned... but I'll deal with that later :)

Thanks again