473,324 Members | 2,239 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

MakeTableQuery

n8kindt
221 100+
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
9 2036
rsmccli
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
2,653 Expert 2GB
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
221 100+
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
32,556 Expert Mod 16PB
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
221 100+
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
2,653 Expert 2GB
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
32,556 Expert Mod 16PB
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
2,653 Expert 2GB
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
32,556 Expert Mod 16PB
Great news & thanks :)
Apr 24 '08 #10

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

Similar topics

2
by: johnds | last post by:
I am researching the use of physician services that are recorded in a billing database of over 10 million records. I am only interested in the patient's treatment type, and their attributes (age...
6
by: sara | last post by:
I hope someone can help with this. Our director wants to have a report that will have the departments (Retail stores) across the top, stores down the side and the RANKING of the YTD dept sales...
5
by: amanda27 | last post by:
In my database I have a form that you go to enter project status for week ending. In that form I have a subform so that when you pick a project that it brings in the status entered for last week...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.