Using Access for my data

[Djblois]

I currently use vlookups on an excel workbook to gather information for my add-in but I am trying to change this to quearing an access database instead. First, a question should I use ADO or QueryTable Objects. What are the strengths and weaknesses to each? Currently I am trying the QuertTable Object way and this is the code I have typed out of a book:

Sub RetrieveCustomers()
Dim strConn As String
Dim strSQL As String
Dim qt As QueryTable

strDbPath = "ODBC;DSN=MS Access Database;" & _
"DBQ=H:\@Business_Reporting_Today\References\Atalanta Reference.mdb"

Set qt = ActiveSheet.QueryTables.Add(Connection:=strConn, _
Destination:=ActiveSheet.Range("A1"))

qt.CommandText = "Select * FROM Customer WHERE (Customer.Cust#=7777)"
qt.Refresh

End Sub

The Table Name is Customer and the column name to look at is Cust# (I made this the primary key for that table as well) I have a customer with the number 7777. I ultimately want to be able to look up customer numbers in one column and fill the rest of the information into the spreadsheet for each column on each row. Exactly what I do with the Vlookups currently. Right now I am just learning this topic so I started small and I can't get it to work. I am just trying to return the customer name that has a cust# of 7777

[Carl A]

Try this I changed the name of your database to Atlanta as opposed to Atalanta I just assumed that it was a typo

[Djblois]

Carl,

Thank you. Lol it actually it is Atalanta, that is my company name. I am new to working with Databases, so bare with me as these connections currently look greek to me.

Now I need to check a Column full of codes and fill info after it. Say Column A2-A52 have codes (The size of the columns will change but I know how to make it variable). I need to fill each row with the information of the cust# in Column A. Also, I need to choose which info goes it which column. That worked perfectly for test one. I will pick it up but it will take a little looking into.

I was thinking about using a loop, to look at each cust#? is that the right way to go? Here is what I tried:

Public Sub RetrieveCustomerInfo()
Dim conn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim sSQL As String
Dim fldCount As Long
Dim recCount As Long
Dim custNo As Integer
Dim i As Long

'Set the connection
Set conn = New ADODB.Connection
conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=H:\@Business_Reporting_Today\References\Atalanta Reference.mdb;"

'This is what I thought might work to check Column A
For i = 2 To 5
custNo = Cells(i, 1).Value
'Create sSQL string
sSQL = "SELECT * FROM Customer WHERE CustID = " & custNo & ";"
Next

'Open the connection
conn.Open
'Create a recordset and set the CursorLocation property for record navigation
Set rst = New ADODB.Recordset
rst.CursorLocation = adUseClient
'Fetch data
rst.Open Source:=sSQL, ActiveConnection:=conn, CursorType:=adOpenForwardOnly, _
LockType:=adLockOptimistic, Options:=adCmdUnknown

For recCount = 1 To rst.RecordCount
For fldCount = 1 To rst.Fields.Count
Worksheets("Sheet1").Range("B2").Select
Worksheets("Sheet1").Cells(recCount + 1, fldCount).Value = rst.Fields(fldCount - 1).Value
Next fldCount
rst.MoveNext
Next recCount
'Close connection
conn.Close
Exit Sub
End Sub

Currently it only fills the top row but it fills it with the bottom rows information.

[Carl A]

Here is the sub redone to what I think you wanted. It is not the most elegant available I'm sure. I'm still learning this Beast Excel myself.
I included the test database I used just unzip to your desktop.


Public Sub RetrieveCustomerInfo()
Dim conn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim sSQL As String
Dim fldCount As Long
Dim recCount As Long
Dim custNo As Integer

Dim rngCustNumbers As Range
Dim custNum As Range
'Set the connection
Set conn = New ADODB.Connection
'change to suit
conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Documents and Settings\Owner\Desktop\Atalanta Reference.mdb;"
'H:\@Business_Reporting_Today\References
conn.Open

With ActiveSheet
Set rngCustNumbers = .Range("A2:A" & .Cells(.Rows.Count, 1).End(xlUp).Row)

End With
'Select Starting Cell
ActiveSheet.Range("B2").Select

'Goes through range A2 to A
For Each custNum In rngCustNumbers
custNo = custNum
sSQL = "SELECT * FROM Customer WHERE CustID = " & custNo & ";"

'Create a recordset and set the CursorLocation property for record navigation
Set rst = New ADODB.Recordset
rst.CursorLocation = adUseClient

'Fetch data
rst.Open Source:=sSQL, ActiveConnection:=conn, CursorType:=adOpenForwardOnly, _
LockType:=adLockOptimistic, Options:=adCmdUnknown

For recCount = 1 To rst.RecordCount
For fldCount = 1 To rst.Fields.Count
ActiveCell.Cells(recCount, fldCount).Value = rst.Fields(fldCount - 1).Value
Next fldCount
Next recCount

'Reselect B2 and find the next empty Cell down from B2
ActiveSheet.Range("B2").Select
Do While Not IsEmpty(ActiveCell)
ActiveCell.Offset(1, 0).Select
Loop

Next custNum
Set rngCustNumbers = Nothing


'Close connection
conn.Close
Set conn = Nothing
Exit Sub
End Sub

HTH