Get a value from the last record of an external table 1

[Moss100]

Hello all,

I need to get the value BALANCE from the last record in an external (not linked) table called tblACCOUNT

The table has a unque identity called REFERENCE

I think I will need to open the external table, sort it by REFERENCE and then go to the last record and obtain the BALANCE value

Help much appreciated. Thanks Mark

 

[dhookom]

I would probably write a query like:

SELECT TOP 1 Balance
FROM tblACCOUNT IN '[Your full file name here]'
ORDER BY REFERENCE DESC

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Moss100]

Thank you for your continued help.

I am wanting to set up a ADODB connection as it seems to avoid a lock being placed on the external database.

I have so far muddled together the following code - I think it is making the connection, but I was hoping the messagebox would display the BALANCE of the last record - but no joy.....

I am happy to use DAO if you advise its better than ADO though.

Dim cnn As New ADODB.Connection
Dim ACrecordset As ADODB.Recordset
Dim CnnStr As String
Dim strSQL As String
 
CnnStr = "Provider=Microsoft.Jet.OLEDB.4.0;"
CnnStr = CnnStr + "User ID=Admin;"
CnnStr = CnnStr + "Data Source=c:\Gems.mdb"
 
Set cnn = New ADODB.Connection
cnn.ConnectionString = CnnStr
cnn.CursorLocation = adUseNone
cnn.Open
 
Set ACrecordset = New ADODB.Recordset
With ACrecordset
    .CursorType = adOpenStatic
    .CursorLocation = adUseClient
    .LockType = adLockReadOnly
 
   
    strSQL = "Select * FROM [" & Me.[txt_Ac_Ref].Value
    strSQL = strSQL & "] ORDER BY date desc"

Dim BAL As Variant
BAL = "BALANCE"

MsgBox BAL

 

[dhookom]

I don't get any lock issues when I have the testing table open from the other database and attempt to run the local query.
[ul]
[li]Why do you think you need to use code? You could use the query with DLookup()[/li]
[li]Does your SQL look like mine?[/li]
[li]Does it select a single field and a single record?[/li]
[li]Why order by Date when you suggested you wanted it ordered by REFERENCE?[/li]
[li]Why create strSQL and then not set it as a property of the recordset?[/li]
[/ul]

Your code will return the value "BALANCE" because you tell it to in the second to the last row.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Moss100]

OK, so the code below seems to be running.

I have now sorted the records by DATE first and then on REFERENCE to ensure I am getting the latest (at least date wise record).

I still think I may need to explicitly select the last record, but not sure how to do this. At present I think my code is just by chance via the sort order selecting the last record.

Any obvious faults or improvements? Its pretty important to my project that I get this right. Many thanks Mark

Dim cnn As New ADODB.Connection
Dim ACrecordset As ADODB.Recordset
Dim CnnStr As String
Dim strSQL As String
 
CnnStr = "Provider=Microsoft.Jet.OLEDB.4.0;"
CnnStr = CnnStr + "User ID=Admin;"
CnnStr = CnnStr + "Data Source=c:\Gems.mdb"
 
Set cnn = New ADODB.Connection
cnn.ConnectionString = CnnStr
cnn.CursorLocation = adUseNone
cnn.Open
 
Set ACrecordset = New ADODB.Recordset

With ACrecordset
    .CursorType = adOpenStatic
    .CursorLocation = adUseClient
    .LockType = adLockReadOnly
 
   
    strSQL = "Select * FROM [" & Me.[txt_Ac_Ref].Value
    strSQL = strSQL & "] ORDER BY date desc, reference desc"
    
.Open strSQL, cnn

 Set ACrecordset.ActiveConnection = Nothing
 
 If .RecordCount > 0 Then
 Set Me.Recordset = ACrecordset
   

Dim BAL As Variant
BAL = [BALANCE]

MsgBox BAL
  

  End If
   
End With
 
 Set ACrecordset = Nothing
 cnn.Close
 Set cnn = Nothing

 

[Moss100]

Hello Duane,

Sorry I posted without seeing your reply.

I discovered that I was after the latest record by date, not necessarily the latest record by ID (or Reference in this case).

I have therefore ordered by DATE DESC and then by REFERENCE DESC - hopefully that should OK.

Will Dlookup allow me to do all this, if so that may be easiest.

Many thanks Mark

 

[MajP]

Write the sql or link the table. The code gets you nothing in resource savings. Also read about record locking, it does not sound as if you understand it because your assumptions are incorrect.

 

[Moss100]

Thanks for sticking with me.

I am unable to link the table to the database. The 'backend' has over 4000 tables (each identical account tables). I understand that this is not normalised, but it is a professional program and I am unable to change its format.

I have tried to run Duanes code from a button on a test form, but can get it to run - clearly i'm getting this wrong.

Private Sub btn_Runcode2_DblClick(Cancel As Integer)

Dim strSQL As String

strSQL = "SELECT TOP 1 Balance" &; _
"FROM 1000 IN 'c:\gem.mdb'" &; _
"ORDER BY 'REFERENCE DESC'"

DoCmd.RunSQL (strSQL)

End Sub

 

[dhookom]

Moss100, it would have helped tremendously if you would have mentioned in the first post that the table name would be dynamic since you have multiple similar tables. It was a significant fact that you held back.

Your code is missing a spaces between expression and has single quotes around 'REFERENCE DESC' so it resolves to:

SELECT TOP 1 BalanceFROM 1000 In 'c:\gem.mdb'ORDER BY 'REFERENCE DESC'"

What's up with the two "[highlight #FCE94F];[/highlight]"? These need to be removed.

Simply create a saved query with any sql since it will be changed by your code. Then add code like:

Dim strSQL as String
Dim db as DAO.Database
Dim qd as DAO.QueryDef
Dim varBalance as Variant
Set db = Currentdb
Set qd = db.QueryDefs("your query name here")
strSQL = "SELECT TOP 1 Balance " & _
    "FROM [" & Me.[txt_Ac_Ref] "] IN 'c:\Gems.mdb' " & _
    "ORDER BY [Date] DESC, REFERENCE DESC" 
debug.print strSQL  [COLOR=#4E9A06]'for debugging[/color]
qd.SQL = strSQL
varBalance = DLookup("Balance","your query name here")

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Moss100]

Thank very much for your help. Mark