squaredealb
08-08-2007, 06:11 AM
Hi All,
I'm not sure if this is the right forum for this topic but I'll give it a shot...
I am trying to run a query of an Access table from excel. i would like to set it up to limit the data returned to excel to records that correspond to values that are present in a range of cells. I recorded a macro of importing the query and then modified the query slightly in the VBA code.
Here's what I have so far:
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 8/6/2007 by SQUAREDEALB
'
'
criteria = Range("A1").Value
With ActiveSheet.QueryTables.Add(Connection:=Array( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=C:\Documents and Settings\sqdb\My Documents\sqdbTest." _
, _
"mdb;Mode=Share Deny Write;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Pass" _
, _
"word="""";Jet OLEDB:Engine Type=5;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Tran" _
, _
"sactions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:" _
, _
"Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False" _
), Destination:=Range("B1"))
.CommandType = xlCmdTable
.CommandText = Array( _
"SELECT Table1.PROJECT_ID, Table1.EMPLOYEE_ID, Table1.DETAILS FROM Table1 where barcode_id=" & criteria)
.Name = "sqdbTest_2"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = False
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.SourceDataFile = _
"C:\Documents and Settings\sqdb\My Documents\sqdbTest.mdb"
.Refresh BackgroundQuery:=False
End With
End Sub
With the macro/query set up in this way, I am able to extract the data for the value in cell A1. When I try to set this as a range of more than one cell, I get an error. Any ideas on how I would go about doing this? I'm still very new to this type of stuff so any suggestions or comments on what I've got so far are greatly appreciated. Thanks for the help.
Jeff
I'm not sure if this is the right forum for this topic but I'll give it a shot...
I am trying to run a query of an Access table from excel. i would like to set it up to limit the data returned to excel to records that correspond to values that are present in a range of cells. I recorded a macro of importing the query and then modified the query slightly in the VBA code.
Here's what I have so far:
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 8/6/2007 by SQUAREDEALB
'
'
criteria = Range("A1").Value
With ActiveSheet.QueryTables.Add(Connection:=Array( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=C:\Documents and Settings\sqdb\My Documents\sqdbTest." _
, _
"mdb;Mode=Share Deny Write;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Pass" _
, _
"word="""";Jet OLEDB:Engine Type=5;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Tran" _
, _
"sactions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:" _
, _
"Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False" _
), Destination:=Range("B1"))
.CommandType = xlCmdTable
.CommandText = Array( _
"SELECT Table1.PROJECT_ID, Table1.EMPLOYEE_ID, Table1.DETAILS FROM Table1 where barcode_id=" & criteria)
.Name = "sqdbTest_2"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = False
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.SourceDataFile = _
"C:\Documents and Settings\sqdb\My Documents\sqdbTest.mdb"
.Refresh BackgroundQuery:=False
End With
End Sub
With the macro/query set up in this way, I am able to extract the data for the value in cell A1. When I try to set this as a range of more than one cell, I get an error. Any ideas on how I would go about doing this? I'm still very new to this type of stuff so any suggestions or comments on what I've got so far are greatly appreciated. Thanks for the help.
Jeff