Excel UDF the Returns Data from Access DB

[sbrink1414]

I would like to create UDF's in Excel that return a single piece of data from an Access table and/or query. For example, I would like to reference a serial number in an Excel cell, and return a price stored in an Access table for that serial number.

I am a VBA beginner, and have created a few UDF's in Excel, but I am lost when it comes to returning external data. Thanks for your help.

 

[chicagocomputerclasses]

Try this. You may need to add an if statement and make sure the data in the cell is in accurate format before you run the query.

Public Function MyUDFName(sSerial As String)
    Set cn = CreateObject("ADODB.Connection")
    strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=C:folder\accessfile.mdb"
    strSql = "SELECT price FROM tablename WHERE((serialnumber)='" + sSerial + "');"
    cn.Open strConnection
    Set rs = cn.Execute(strSql)
   MyUDFName = rs.Fields("price").Value
End Function

 

[chicagocomputerclasses]

Make sure you update the file path and replace with accurate access query.

 

[sbrink1414]

This works great for strings, but doesn't work for number values. For example, the format of the data in Access is "Number". I have tried Doubls, Long, integer, etc. and I get a #VALUE error? Not sure how to structure the If statement.

Public Function MyUDFName(sSerial As String)

 

[chicagocomputerclasses]

I think the problem you are having is with your Where clause. For strings you use quotes like = 'myserialnumberasstring', however for numbers Access doesn't use quotes so it goes like WHERE (((fieldname)=2)); with no apostrophes.

strSql = "SELECT price FROM tablename WHERE(((serialnumber)=" + sSerial + "));"

 

[sbrink1414]

That didn't seem to work. Here's the format of the field in the Access table.

Field Size Decimal
Format Standard
Precision 19
Decimal Places Auto

This is a linked table, and Access will not allow me to change some of these settings.

 

[chicagocomputerclasses]

So you are saying from the same lined table the query works if it's text? Is that correct?

I've noticed with some ODBC linked tables from Access the connection won't pull the data at all. But in case it does pull it from the same table when it's text then you should be fine.

Try to do this: go to Access and run a sample query with a query builder that retrieves the data you want. Change it to SQL view and let me know what the query is.

strSql variable in this is just a string and you run it as a string, so as long it builds the right string for the query it must to work.

 

[sbrink1414]

So I was messing around with the function that was not working. I replaced the field that had numeric values with a field with text, and added the apostrophe's back in, and it ran. I then changed the field back to the numeric field, and now it runs. Weird.

 

[chicagocomputerclasses]

Hah. Well, as long as it's working.

 

[sbrink1414]

I know I'm doing this wrong, but I tried! I want to return a price for a serial number, but from a certain loacation. The same serial numbers can come from different locations.

Public Function MyUDF(sSerial As String)
Set cn = CreateObject("ADODB.Connection")
strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=H:\BT\SEC\data.mdb"
strSql = "SELECT Price" _
& "FROM dbo_product" _
& "WHERE Location = Boston" _
& " and((Serial)='" + sSerial + "');"
cn.Open strConnection
Set rs = cn.Execute(strSql)
MyUDF = rs.Fields("Price").Value
End Function