Working in Access 2007 and Excel 2007....
I have an .accdb (WellCardPilot.accdb) that pulls information from multiple tables (some linked, some built into the DB). The following code works fine to return all records from a query called BridgeportWellCard to a specific worksheet in Excel (Sheet 2). In this case its 50 unique records. I would like to be able to Query the results of the Access Query and have only 1 record returned based on a value that I type into 1) a cell, 2) a pull down box, 3) a message box, etc....I don't really care as long as I can enter a value somewhere, run the query, and return 1 record instead of 50. Here's my code...I know I will have to change when it executes from "open workbook" to something else...I'm more concerned with how to query my query results
I suspect I can do something with the red line of code but just not sure..any help is appreciated!!
Private Sub Workbook_Open()
Dim TARGET_DB As String
Dim cnn As ADODB.connection
Dim rcs As ADODB.Recordset
Dim mQry As String
'mQry = "select * from [DB$]"
TARGET_DB = "WellCardPilot.accdb"
mQry = "BridgeportWellCard"
Set cnn = New ADODB.connection
Set rcs = New ADODB.Recordset
'create the connection to the database
myconn = ThisWorkbook.Path & Application.PathSeparator & TARGET_DB
With cnn
.Provider = "Microsoft.ACE.OLEDB.12.0"
.Open myconn
End With
With rcs
.Open mQry, cnn
End With
MsgBox rcs.Fields.Count
With Sheet2
.Activate
Range("A2", ["XFD1048376"]).Clear
MsgBox ("Cool")
Cells(2, 1).CopyFromRecordset rcs
End With
rcs.Close
cnn.Close
End Sub
For what its worth, I tried building a criteria into my Access Query by typing "[Well#]" into the Query Design which, in Access, requires the user to type a number into a pop up box (e.g. 255), therby returning only 1 unique record (e.g. for Well # 255). Then I tried this lone of code somewhere "Application.DisplayAlerts = True" and I got an error...I'm ready to slam my forehead into my computer to see which one is stronger......
I have an .accdb (WellCardPilot.accdb) that pulls information from multiple tables (some linked, some built into the DB). The following code works fine to return all records from a query called BridgeportWellCard to a specific worksheet in Excel (Sheet 2). In this case its 50 unique records. I would like to be able to Query the results of the Access Query and have only 1 record returned based on a value that I type into 1) a cell, 2) a pull down box, 3) a message box, etc....I don't really care as long as I can enter a value somewhere, run the query, and return 1 record instead of 50. Here's my code...I know I will have to change when it executes from "open workbook" to something else...I'm more concerned with how to query my query results
I suspect I can do something with the red line of code but just not sure..any help is appreciated!!Private Sub Workbook_Open()
Dim TARGET_DB As String
Dim cnn As ADODB.connection
Dim rcs As ADODB.Recordset
Dim mQry As String
'mQry = "select * from [DB$]"
TARGET_DB = "WellCardPilot.accdb"
mQry = "BridgeportWellCard"
Set cnn = New ADODB.connection
Set rcs = New ADODB.Recordset
'create the connection to the database
myconn = ThisWorkbook.Path & Application.PathSeparator & TARGET_DB
With cnn
.Provider = "Microsoft.ACE.OLEDB.12.0"
.Open myconn
End With
With rcs
.Open mQry, cnn
End With
MsgBox rcs.Fields.Count
With Sheet2
.Activate
Range("A2", ["XFD1048376"]).Clear
MsgBox ("Cool")
Cells(2, 1).CopyFromRecordset rcs
End With
rcs.Close
cnn.Close
End Sub
For what its worth, I tried building a criteria into my Access Query by typing "[Well#]" into the Query Design which, in Access, requires the user to type a number into a pop up box (e.g. 255), therby returning only 1 unique record (e.g. for Well # 255). Then I tried this lone of code somewhere "Application.DisplayAlerts = True" and I got an error...I'm ready to slam my forehead into my computer to see which one is stronger......
