By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,475 Members | 2,060 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,475 IT Pros & Developers. It's quick & easy.

MakeTableQuery

n8kindt
100+
P: 221
i have a query that takes a while to run so i am creating a maketable query. i am going to place a button on a form and in the OnClick event i am going to run the maketable using the following code:

Expand|Select|Wrap|Line Numbers
  1. DoCmd.SetWarnings False
  2. DoCmd.OpenQuery "ManagerSummary"
  3. DoCmd.SetWarnings True
this is where my expertise ends and i'm hoping i can have some help picking up the slack. before the query is run i want a window a msgbox to pop up and inform the user of when the last time this table was updated (created) and ask if they would like to proceed or not.

one other small detail i'm working with is this table is in another database. idk if that will make any difference or not.

thanks in advance for all your replies!!
Apr 18 '08 #1
Share this Question
Share on Google+
9 Replies


rsmccli
P: 52
If the time of table creation is being stored in the old made table (the one about to be overwritten), I guess you would set that table as your recordset and check when the creation date was, then have that show up in your messagebox. Then have Yes or No buttons on your messagebox. This may be overkill, but I would do it this way.
Expand|Select|Wrap|Line Numbers
  1. Dim db As DAO.Database  
  2. Dim rs As DAO.Recordset 
  3. Dim strCreationDate As String
  4. 'or perhaps as a Date if you prefer
  5.  
  6. Set db = CurrentDb()  
  7. Set rs = db.OpenRecordset("tblManagerSummary")
  8.  
  9. With rs
  10.    If Not .BOF And Not .EOF Then
  11.       .Movefirst
  12.       strCreationDate = rs.Fields("CreationDate")
  13.    Else
  14.       strCreationDate = "No Records In Source Table"
  15.    End If
  16.    .close
  17. End With
  18.  
  19. If MsgBox ("These records were last updated on: " & strCreationDate & vbcrlf & vbcrlf & "Proceed?", vbYesNo) = vbYes Then
  20.    DoCmd.SetWarnings False
  21.    DoCmd.OpenQuery "ManagerSummary"
  22.    DoCmd.SetWarnings True
  23. Else
  24. End If  
Again, I am no master and there is probably an easier way to do this. If your date is being stored somewhere else then post where it is being stored.

HTH,
rsmccli
Apr 21 '08 #2

FishVal
Expert 2.5K+
P: 2,653
Hi, n8kindt.

If by "Updated" you mean table design changes, then you may use TableDef class DateCreated and LastUpdated properties.
Expand|Select|Wrap|Line Numbers
  1. Dim db As DAO.Database
  2. Set db = DBEngine.OpenDatabase("...path to database ...")
  3. Debug.Print db.TableDefs("[Table Name]").DateCreated
  4. Debug.Print db.TableDefs("[Table Name]").LastUpdated
  5. Set db = Nothing
  6.  
If by "Updated" you mean data modification, then I think Access is not likely to provide a convenient way to do it. You should have timestamp field in the table to save time of record insertion/update.

Regards,
Fish
Apr 22 '08 #3

n8kindt
100+
P: 221
If the time of table creation is being stored in the old made table (the one about to be overwritten), I guess you would set that table as your recordset and check when the creation date was, then have that show up in your messagebox. Then have Yes or No buttons on your messagebox. This may be overkill, but I would do it this way.
Expand|Select|Wrap|Line Numbers
  1. Dim db As DAO.Database  
  2. Dim rs As DAO.Recordset 
  3. Dim strCreationDate As String
  4. 'or perhaps as a Date if you prefer
  5.  
  6. Set db = CurrentDb()  
  7. Set rs = db.OpenRecordset("tblManagerSummary")
  8.  
  9. With rs
  10.    If Not .BOF And Not .EOF Then
  11.       .Movefirst
  12.       strCreationDate = rs.Fields("CreationDate")
  13.    Else
  14.       strCreationDate = "No Records In Source Table"
  15.    End If
  16.    .close
  17. End With
  18.  
  19. If MsgBox ("These records were last updated on: " & strCreationDate & vbcrlf & vbcrlf & "Proceed?", vbYesNo) = vbYes Then
  20.    DoCmd.SetWarnings False
  21.    DoCmd.OpenQuery "ManagerSummary"
  22.    DoCmd.SetWarnings True
  23. Else
  24. End If  
Again, I am no master and there is probably an easier way to do this. If your date is being stored somewhere else then post where it is being stored.

HTH,
rsmccli
i appreciate your reply! yes, that answers (and solves) the majority of my questions. i do see a minor (ehhhh could be major) problem. i am not at the office right now but as i see it, this will store the date in every single recordset? i already have 5000 records in this query so this doesn't sound exactly ideal. i'm thinking maybe i should run the said maketable query and then run an update query on the same table where it will only stamp the date into the first recordset (ID = 1). not the most efficient i suppose but it will probably work. thanks for posting such a helpful response!
Apr 22 '08 #4

NeoPa
Expert Mod 15k+
P: 31,186
Fish's response should work for you, but you'd need to get the address of the BackEnd database (LocalLinkedTable.Connect) first as you've said this is a linked database.

Using a MakeTable query will NOT create a new linked table for you.

I use a separate table to handle my dates. Involves a bit of supporting code etc, but suits my requirements.

Fish's references are good, and I used to use them more heavily. Unfortunately a Compact/Repair seems to reset dates for all abjects to the C/R date thus losing the info I required :(
Apr 23 '08 #5

n8kindt
100+
P: 221
Fish's response should work for you, but you'd need to get the address of the BackEnd database (LocalLinkedTable.Connect) first as you've said this is a linked database.

Using a MakeTable query will NOT create a new linked table for you.

I use a separate table to handle my dates. Involves a bit of supporting code etc, but suits my requirements.

Fish's references are good, and I used to use them more heavily. Unfortunately a Compact/Repair seems to reset dates for all abjects to the C/R date thus losing the info I required :(
wow i didnt even notice fish's reply until i read your comments! looks like he posted jjjust before i did. i haven't had a chance to touch my database lately anyways. and i will definitely give that a try. it just might work because...

after i first ran the maketable query initially, i linked the table to the front end database. so now, when i run the maketable query it copies over it (b/c the table name is continually the same) in the backend and the link still seems to work fine in the frontend as it is never broken off by access. i have access 2007... maybe that makes a difference? anyways, i'm not terribly worried about the date/time being off when it is compacted. this will work most of the time. i will give it a try and let u all know how it turns out. and by the way, yes, i am talking about table modification--not the data within the table. thanks fish!
Apr 24 '08 #6

FishVal
Expert 2.5K+
P: 2,653
You are welcome.
BTW: I didn't notice C/R resets the dates of table creation/modification. At least in Access 2003.
Apr 24 '08 #7

NeoPa
Expert Mod 15k+
P: 31,186
I'm unclear as to what you're saying Fish. Do you mean that C/R DOESN'T reset dates in A2003 (I must admit I noticed this at work where I still use A2000)?

If that's true then it's great news. I'd love to be able to use it more often without losing the info.

@N8kindt
Don't worry about that info for your issue. I only mentioned it to explain that I don't use C/R much anymore because I DO use that data in my database (for management) and C/R loses it all for me :(
Apr 24 '08 #8

FishVal
Expert 2.5K+
P: 2,653
I'm unclear as to what you're saying Fish. Do you mean that C/R DOESN'T reset dates in A2003 (I must admit I noticed this at work where I still use A2000)?

If that's true then it's great news. I'd love to be able to use it more often without losing the info.
Yes. In Access 2003 TableDef.DateCreated and TableDef.LastUpdated properties return the same before and after C/R.

Regards,
Fish.
Apr 24 '08 #9

NeoPa
Expert Mod 15k+
P: 31,186
Great news & thanks :)
Apr 24 '08 #10

Post your reply

Sign in to post your reply or Sign up for a free account.