How do you reference a record within a table in Access using VBA?

[gaikminn]

Hi,

I have a series of small tables in Access, with table names in the following format ABCYYYYMMDD (eg: ABC20080124). I also have a table named tblMaster which has a date field.

I'm writing a VBA routine where if the date of the small table already existed in tblMaster, the small table is deleted.

I need help with debugging the following code:

Sub CheckDate()

Dim strSQL As String
Dim db As DAO.Database
Dim qd As DAO.QueryDef
Dim tdf As DAO.TableDef
Set db = CurrentDb

For Each tdf In db.TableDefs

If Right(tdf.Name, 2) & "/" & Mid(tdf.Name, 8, 2) & "/" & Mid(tdf.Name, 4, 4) = tblMaster.Date

Then


strSQL = "DROP TABLE " & tdf.Name
db.Execute (strSQL), dbFailOnError

[missinglinq]

First off, your post would indicate that you have a field name Date. If this is true, you have to change this to something else! Date is a Reserved Word in Access and a dangerous one! Change it to something like TDate for table date. For an in depth explanation look here.

Next, your code, using the table name ABC20080124 will compare your date field to 24/01/2008. Is this the format of your date field in the tblMaster table?

Welcome to TheScripts!

Linq ;0)>

[gaikminn]

Hi Linq,

Thank you so much for your help. I've changed the field names in both the series of small tables and tblMaster from Date to TDate. And yes, you are right, the format of the TDate field is 24/01/2008 (ie Short Date).

However, I still can't get the code to work. Any help would be greatly appreciated!

Thanks a lot!
MGN

First off, your post would indicate that you have a field name Date. If this is true, you have to change this to something else! Date is a Reserved Word in Access and a dangerous one! Change it to something like TDate for table date. For an in depth explanation look here.

Next, your code, using the table name ABC20080124 will compare your date field to 24/01/2008. Is this the format of your date field in the tblMaster table?

Welcome to TheScripts!

Linq ;0)>

[TerryDM]

Hi,

I have a series of small tables in Access, with table names in the following format ABCYYYYMMDD (eg: ABC20080124). I also have a table named tblMaster which has a date field.

I'm writing a VBA routine where if the date of the small table already existed in tblMaster, the small table is deleted.

I need help with debugging the following code:

Sub CheckDate()

Dim strSQL As String
Dim db As DAO.Database
Dim qd As DAO.QueryDef
Dim tdf As DAO.TableDef
Set db = CurrentDb

For Each tdf In db.TableDefs

If Right(tdf.Name, 2) & "/" & Mid(tdf.Name, 8, 2) & "/" & Mid(tdf.Name, 4, 4) = tblMaster.Date

Then


strSQL = "DROP TABLE " & tdf.Name
db.Execute (strSQL), dbFailOnError

I'ld do it this way on the 'If' just to eliminate any conversion problems:

If Right(tdf.Name,8) = Year(tblMaster.Date) & Month(tblMaster.Date) & Day(tblMaster.Date) then

This way it is 2 strings being compared.

Also have you looked at the input to the If in a MSgBox? How does tblMaster.Date get set - there's nothing in the Sub?

[gaikminn]

Hi Terry,

My major problem is I don't know how to define the values of the field named "Date" in tblMaster using VBA in Access. Is it something like


Sub CheckDate()

Dim strSQL As String
Dim db As DAO.Database
Dim qd As DAO.QueryDef
Dim tdf As DAO.TableDef
Dim table As DAO.TableDef

Set db = CurrentDb

Set table = tblMaster

For Each tdf In db.TableDefs

If Right(tdf.Name,8) = Year(tblMaster.Date) & Month(tblMaster.Date) & Day(tblMaster.Date)

Then

strSQL = "DROP TABLE " & tdf.Name
db.Execute (strSQL), dbFailOnError

End If

End Sub

Would really appreciate your help! Thank you so much!
MGN

I'ld do it this way on the 'If' just to eliminate any conversion problems:

If Right(tdf.Name,8) = Year(tblMaster.Date) & Month(tblMaster.Date) & Day(tblMaster.Date) then

This way it is 2 strings being compared.

Also have you looked at the input to the If in a MSgBox? How does tblMaster.Date get set - there's nothing in the Sub?

[TerryDM]

Guess I was looking at the wrong 'problem'. Try something like this:

For Each tdf In db.TableDefs
tdate = CDATE(Right(tdf.Name, 2) & "/" & Mid(tdf.Name, 8, 2) & "/" & Mid(tdf.Name, 4, 4))
 
strSQL = "SELECT * FROM tblMaster WHERE TDate = '" & tdate &"';"
Set rs = db.Execute (strSQL)
 
If not rs.EOF and not rs.BOF then
strSQL = "DROP TABLE " & tdf.Name
db.Execute (strSQL), dbFailOnError
End If
 

[gaikminn]

Hi,

I've defined rs as DAO.Recordset, then Set rs = db.OpenRecordset(strSQL) as the following shows. I've also included date literals, ie the # characters in front of and behind tdate as below. But I still can't get the code to work? Note: I have kept the field name 'Date' in tblMaster because when I tried the same code below with field name 'TDate' in tblMaster, it didn't work either.

Sub EOFBOF()

Dim strSQL As String
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim tdate As Date
Dim rs As DAO.Recordset

On Error GoTo Proc_Error

Set db = CurrentDb

For Each tdf In db.TableDefs


tdate = CDate(Right(tdf.Name, 2) & "/" & Mid(tdf.Name, 8, 2) & "/" & Mid(tdf.Name, 4, 4))

strSQL = "SELECT * FROM tblMaster WHERE (tblMaster.Date) = #" & tdate & "#;"

Set rs = db.OpenRecordset(strSQL)

If Not rs.EOF And Not rs.BOF Then

strSQL = "DROP TABLE " & tdf.Name
db.Execute (strSQL), dbFailOnError

End If

Next tdf

Proc_Exit:
Exit Sub

Proc_Error:
Resume Proc_Exit

End Sub

Would really appreciate your help on this, thank you so much!
MGN

Guess I was looking at the wrong 'problem'. Try something like this:

For Each tdf In db.TableDefs
tdate = CDATE(Right(tdf.Name, 2) & "/" & Mid(tdf.Name, 8, 2) & "/" & Mid(tdf.Name, 4, 4))
 
strSQL = "SELECT * FROM tblMaster WHERE TDate = '" & tdate &"';"
Set rs = db.Execute (strSQL)
 
If not rs.EOF and not rs.BOF then
strSQL = "DROP TABLE " & tdf.Name
db.Execute (strSQL), dbFailOnError
End If