using access database in excel

[amypaulsen]

I am trying to pull access into excel using SQL while referencing a cell within my spreadsheet to reference. below is the code that I am using...and while it looks like something is happening...nada

can you help?

Private Sub cmdRun_Click()
Dim objAccess As Object
Dim objDB As Object
Dim rst As Object
Dim strSQL As String


Sheets("Current Week").Select
Set objAccess = CreateObject("access.application")

With objAccess
.opencurrentdatabase (ActiveWorkbook.Path & "\Flash FY05.mdb")


BeginLoop:

Set objDB = objAccess.currentdb()

With objDB
strSQL = "SELECT tblTransactions.UNIT, tblTransactions.W51 from [tblTransactions] where tblTransactions.UNIT = '" & Cells(12, 35).Value & "'"



Set rst = objDB.openrecordset(strSQL)

If Not rst.RecordCount = 0 Then

rst.movefirst


Do


colnum = 32
rownum = 20
Sheets("current week").Select



If Cells(12, colnum).Value = rst.fields("UNIT").Value Then

For Each fld In rst.fields

colnum = colnum + 1
Cells(rownum, colnum).Value = rst.fields("W51").Value
Next fld

End If

rst.movenext
Loop Until rst.EOF

End If

End With
End With

rst.Close

End Sub

 

[Norie]

Have you tried stepping through with F8 to see if anything is happening?

 

[amypaulsen]

not really familiar with the f8 thing...i've been learning code on my own and realize that I still have a lot to learn.

when I pushed f8, the form popped up but access never opened, should it have?

i am using very similar code in another program and it works fabulously so I think I'm referencing the cell within the SQL line incorrectly

 

[Norie]

Sorr ymy mistake I didn;t realize it was run of a button.

What do do is:

1 Goto Module tab in Access and create a new module

2 Copy and paste your data in there.

3 Now press F8 or goto Debug>Step Into

Subsequent presses of F8 will step through the code.

As you do this you can hover over variables with the mouse to see what they are.

You can also set up watches on variables by right clicking on them and selecting Add Watch...

If you try this hopefully you'll see where the code is going wrong.

 

[amypaulsen]

I walked through the code and it is successfully opening access. I think I am referencing my data incorrectly within the sql code

 

[Norie]

Did you 'watch' any of the variables? eg rst

That would probably be a good idea, you could see how many records are being pulled from the query.

Another thing is this

Cells(12, 35).Value

You should probably reference it explicitly with workbook and worksheet name, to make sure it's getting the right value.

Also test the SQLl in Access.