snowmantle
Programmer
Hi,
I have written the below sub to insert Access table data into my Excel file.
I want to then be able to write a SQL statement to retrieve a specific recordset from this querytable, then put the result into a two dimensional array and loop through a range in a raw data worksheet doing a find and replace for the warehouse_name.
The SQL to query the new querytable would be something like the below
Does anyone have any examples on how this could be done?
I was hoping not to have to connect to the Access database everytime I need to query for new data because the file may not be accessible when this sub is being run, but if someone could recommend a better alternative than the above then I am all ears.
Links to better information on how to use QueryTables would be much appreciated, all I can seem to find is how they can be added to workbooks.. they seem fairly limited
I have written the below sub to insert Access table data into my Excel file.
Code:
Sub InsertAccessTable(tableName As String)
'
Dim MyDB As String, connStr As String, sqlStr As String
MyDB = ThisWorkbook.path & "\" & "master.mdb"
connStr = "ODBC;DSN=MS Access Database;DBQ=" & MyDB & ";DefaultDir=" & _
ThisWorkbook.path & "\" & ";DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5"
sqlStr = "SELECT * FROM " & tableName
'check if a worksheet already exists, if it does then remove it
If CommonMod.SheetExists(tableName) Then
ActiveWorkbook.Worksheets(tableName).Delete
End If
'add a new sheet
ActiveWorkbook.Worksheets.Add
'loop through and check the desired name is not taken
'if it is then delete it so the name can be added again
Set nms = ActiveWorkbook.Names
For i = 1 To nms.Count
If nms(i).Name = tableName Then
ActiveSheet.Name(tableName).Delete
End If
Next
'set the name of the new sheet to the same as the table name
ActiveSheet.Name = tableName
'adds a new table of data to the new sheet
'using the sql statement provided above
With ActiveSheet.QueryTables.Add(Connection:=connStr, Destination:=Range("A1")) ', SQL:=sqlStr
.CommandText = sqlStr
.Name = tableName
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub
I want to then be able to write a SQL statement to retrieve a specific recordset from this querytable, then put the result into a two dimensional array and loop through a range in a raw data worksheet doing a find and replace for the warehouse_name.
The SQL to query the new querytable would be something like the below
Code:
"SELECT warehouse_name from Warehouse where parent_fkey > 0
Does anyone have any examples on how this could be done?
I was hoping not to have to connect to the Access database everytime I need to query for new data because the file may not be accessible when this sub is being run, but if someone could recommend a better alternative than the above then I am all ears.
Links to better information on how to use QueryTables would be much appreciated, all I can seem to find is how they can be added to workbooks.. they seem fairly limited