CODEWITHSUNDEEP
ms-accessvbams-access-2010
dmorgan20
dmorgan20

Reputation: 363

Pulling MAX date from a separate access database

I am trying to get the MAX date from a separate access database.

Variables:

dbpath = "C:\Portal\_Dev"
dbname = "Portal.accdb"
strpassword = "OpenSesame"
tblEngage = "tbl_engagement"

Code that I have tried:

CurrentDb.Execute "SELECT [MS Access;pwd=" & strpassword & ";database=" & dbpath & "\" & dbname & "].[" & tblEngage & "].CDP, Max([MS Access;pwd=" & strpassword & ";database=" & dbpath & "\" & dbname & "].[" & tblEngage & "].Open_Date) AS MaxOfOpen_Date" & _
         "FROM [MS Access;pwd=" & strpassword & ";database=" & dbpath & "\" & dbname & "].[" & tblEngage & "] " & _
         "WHERE [CDP] = '" & Text285 & "'"

This method I understand won't work due to the SELECT not being told to look in an external table:

CurrentDb.Execute "SELECT tbl_Engagement.CDP, Max(tbl_Engagement.Open_Date) AS MaxOfOpen_Date" & _
         "FROM [MS Access;pwd=" & strpassword & ";database=" & dbpath & "\" & dbname & "].[" & tblEngage & "] " & _
         "WHERE [CDP] = '" & Text285 & "'"

Error Received:

The Select statement includes a reserved word or an argument name that is misspelled or missing

Any help is appreciated

Upvotes: 0

Views: 77

Answers (1)

June7
June7

Reputation: 21370

Open and set a recordset object with SELECT sql statement.

A simple example without password encryption:

Dim rs AS DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT Max(Open_Date) AS MaxOfOpen_Date FROM tbl_Engagement IN '" & dbPath & "\" & dbname & "' WHERE CDP='" & Me.Text85 & "'")
Debug.Print rs!MaxOfOpen_Date

For a db with password, set and open ADODB connection object. This example uses early binding so need the MS ActiveX Data Objects library active. 

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbpath & "\" & dbname & ";Jet OLEDB:Database Password=OpenSesame"
rs.Open "SELECT Max(Open_Date) AS MaxOfOpen_Date FROM tbl_Engagement WHERE CDP='" & Me.Text85 & "';", cn
Debug.Print rs!MaxOfOpen_Date

Upvotes: 2

Related Questions