I am creating a query for Excel to Access Database

[Darren Smith]

I`ve created a VBA query to not find various data see below. How could I make this work?
At the moment the Qry only shows red?

Private Sub Fill_DrNos()

TurnOff

If bReset = True Then Exit Sub


        Dim qry As String
        Dim i As Variant
        Dim prevPos As Long
        
        qry = "SELECT DISTINCT JobCardMaster.F2 FROM JobCardMaster" & _
        " WHERE (JobCardMaster.F2 Is Not Null) " & _
         And JobCardMaster.F2 Not Like "Drawing  No." & _
         And JobCardMaster.F2 Not Like "Drawing No." & "'"

    
        Application.EnableEvents = False
        Dim rs As Object: Set rs = OpenConAndGetRS(qry)
        If Not (rs.BOF Or rs.EOF) Then
            With Me.Print_DrNos
            prevPos = .ListIndex
                .Clear
                Do Until rs.EOF
                    .AddItem (rs.Fields("F2").Value)
                    rs.MoveNext
                Loop
                .ListIndex = prevPos
            End With
       End If
        rs.Close: Set rs = Nothing
 TurnOn
    
End Sub

 

[RoryA]

If you just want the values in the table, you don't need a WHERE clause. What is the table name and which column(s) in the table do you want the data from? Then, where does the data need to go - you mentioned a worksheet and a combobox?

 

[Darren Smith]

The table Name is DrNos & Column Name is DrawingNos needs to populate a VBA Combox called Print_DrNos

 

[RoryA]

Then you could use:

With rst

   .Open "SELECT [DrawingNos] FROM DrNos"
   Me.Print_DrNos.Column = .GetRows

End With

Set rst = Nothing
cnn.Close
Set cnn = Nothing

 

[Darren Smith]

We now have Run-Time error 3709 on the below code Seems to be the " " are stoping it working

.Open "SELECT [DrawingNos] FROM DrNos"

 

[RoryA]

sorry, that line needs a connection object too:

.Open "SELECT [DrawingNos] FROM DrNos", cnn

 

[Darren Smith]

I`ve created a Function for Access Connection to Excel VBA which all works fine.
But for some reason, it won`t allow the below code to work.
Please help

 [CODE=vba]   .Column = .GetRows

 Sub FillDrNos()


TurnOff

    Dim qry As String

    
    qry = "SELECT [DrawingNos] FROM DrNos"
     With Me.Print_DrNos
    
    Dim rs As Object: Set rs = OpenConAndGetRS(qry)
        If Not (rs.BOF Or rs.EOF) Then
        Do While Not rs.EOF
        .Column = .GetRows
        rs.MoveNext
        Loop
        End If
        
        End With
        


rs.Close: Set rs = Nothing
    
    TurnOn
    
End Sub

 

[pjmorris]

Why not use PowerQuery - it would be so much easier.

 

[RoryA]

The code would be:

Sub FillDrNos()
TurnOff

    Dim qry As String
    qry = "SELECT [DrawingNos] FROM DrNos"
    Dim rs As Object: Set rs = OpenConAndGetRS(qry)
    If Not (rs.BOF Or rs.EOF) Then Me.Print_DrNos.column = rs.getrows

rs.Close: Set rs = Nothing
    
    TurnOn
    
End Sub

I suspect your TurnOff and TurnOn calls are completely unnecessary here.

 

[Darren Smith]

I realize that but all my Databases are on Access Database
I would like them to be not Excel-based because everyone here needs to access that Access Database through VBA Coding.

 

[Darren Smith]

It now says Run-time error 70
Permission denied
Do I need a specific Reference for this to work?