CODEWITHSUNDEEP
sqlvbaexcelms-access
Vahid
Vahid

Reputation: 5444

How to pass the table name to sql query in VBA

I'm using the below code to load a table from an MDB database to an excel worksheet. I'm trying to define the table name as variable and pass it to query but I'm getting an error with the code below. How can I do this in VBA?

Public Sub ReadMdb()

  Dim cn As Object, rs As Object
  Dim intColIndex As Integer
  Dim DBFullName As String
  Dim TargetRange As Range

  DBFullName = Application.GetOpenFilename()

  On Error GoTo Oops

  Application.ScreenUpdating = False

  Set TargetRange = Sheets("Sheet1").Range("A1")

  Set cn = CreateObject("ADODB.Connection")
  cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & DBFullName
  Set rs = CreateObject("ADODB.Recordset")

  Dim tableName As String
  tableName = "Students"

  rs.Open "SELECT * FROM tableName", cn, , , adCmdText


  ' Write the field names
  For intColIndex = 0 To rs.Fields.Count - 1
    TargetRange.Offset(1, intColIndex).Value = rs.Fields(intColIndex).Name
  Next

  ' Write recordset
  TargetRange.Offset(1, 0).CopyFromRecordset rs

LetsContinue:
  Application.ScreenUpdating = True
  On Error Resume Next
  rs.Close
  Set rs = Nothing
  cn.Close
  Set cn = Nothing
  On Error GoTo 0
  Exit Sub
Oops:
  MsgBox "Error Description :" & Err.Description & vbCrLf & _
        "Error at line     :" & Erl & vbCrLf & _
        "Error Number      :" & Err.Number
  Resume LetsContinue


End Sub

Upvotes: 0

Views: 3608

Answers (2)

user1644564
user1644564

Reputation: 385

Considering your returning all the rows and all the columns, you could use the command type adCmdTable then you don't need any SQL you simply name the table that you want.

Upvotes: 2

4dmonster
4dmonster

Reputation: 3031

you can insert table name into SQL:

rs.Open "SELECT * FROM [" & tableName & "]", cn, , , adCmdText

Upvotes: 6

Related Questions