How can I check table properties in code? (Access 2K)

Topic

New Reply

Dear all

I’ve just found out that you can’t have two users viewing a linked Excel spreadsheet at the same time. As this is a serious inconvience I plan to import the spread sheet data using the TransferSpreadsheet Action, now is there any way I can speed up subsequent database sessions by checking for when the data table was last updated and then only running my TransferSpreadsheet code if the date isn’t today??

I’ve checked out options like the TableDef Attributes and so on but it’s starting to look like I’m stuck…..

Thanks

Ian

Reply | Quote

Replies

In Access 2002, you can check the DateModified property of CurrentData.AllTables(“YourTableName”), but I think this property didn’t exist in Access 2000.

The DAO TableDef object has a property LastUpdated: CurrentDb.TableDefs(“YourTableName”).LastUpdated.

Reply | Quote

Hans Thanks

With a little bit of code this has proved to be a good first step solution, the next step is to make the first user update the tables in the back end, thus reducing the load on every on else’s Front End. I think this is going to be easy, once I’ve found a few bits of information.

Where would I be able to find the sort of information you just gave me? should i have used the Object Browser? I tried help and the MSKB but couldn’t find the LastUpdated property from table defs. If it’s the Object Browser I can help myself more in future.

Again, Thank You Hans.

Reply | Quote

[indent]

---

the next step is to make the first user update the tables in the back end, thus reducing the load on every on else’s Front End.

---

[/indent] Sorry, but I’m confused by this. Do you have people *sharing* a front end? If so, the way to reduce the load is to give each of them a local front end. Users should not be interacting directly with the backend, only through their front end interface. Otherwise, you lose control over what might be done to the back end.

Reply | Quote

[indent]

---

the next step is to make the first user update the tables in the back end, thus reducing the load on every on else’s Front End.

---

[/indent] Sorry, but I’m confused by this. Do you have people *sharing* a front end? If so, the way to reduce the load is to give each of them a local front end. Users should not be interacting directly with the backend, only through their front end interface. Otherwise, you lose control over what might be done to the back end.

Reply | Quote

Since LastUpdated is a DAO property, you need to select Tools | References… in the Visual Basic Editor and set a reference to the Microsoft DAO 3.6 Object Library to “see” properties such as LastUpdated in the online help and in the Object Browser.

Reply | Quote

Charlotte

I have each user with a seperate front end, but as my current ability is still limited the code I wrote creates the table in trh front end, so every time a user opens a front end it will check the table it has and replace it, in the front end, if it is over 12 hours old. Ideally what the system will do once I’ve completed my search of the Object Browser (thanks Hans) will be to check the table in the back end database and replace it if it is over 12 hours old. This would mean the processing load on each users front end is reduced, with the exception of the early bird of course….

At the moment I’m working on the lines that it is possible to carry out the linked table manager routine from code.

If I get stuck I’ll post the next question, but I try to avoid asking a question until I’ve exhausted all my known avenues, at this point Hans, Yourself, or some other helpfull person shows me the error of my ways and puts me back on track. This method has got me from the stage of “Code, nope not doing that” to “Well, if I put a Do …… Loop in there and chuck a DateDiff in here it’ll sort that out no problem”. This is entirely due to the ‘forgiving’ nature of the lounge towards my questions.

Thanks everybody

Ian

Reply | Quote

That’s what we’re here for, to answer questions.

Reply | Quote

That’s what we’re here for, to answer questions.

Reply | Quote

Charlotte

I have each user with a seperate front end, but as my current ability is still limited the code I wrote creates the table in trh front end, so every time a user opens a front end it will check the table it has and replace it, in the front end, if it is over 12 hours old. Ideally what the system will do once I’ve completed my search of the Object Browser (thanks Hans) will be to check the table in the back end database and replace it if it is over 12 hours old. This would mean the processing load on each users front end is reduced, with the exception of the early bird of course….

At the moment I’m working on the lines that it is possible to carry out the linked table manager routine from code.

If I get stuck I’ll post the next question, but I try to avoid asking a question until I’ve exhausted all my known avenues, at this point Hans, Yourself, or some other helpfull person shows me the error of my ways and puts me back on track. This method has got me from the stage of “Code, nope not doing that” to “Well, if I put a Do …… Loop in there and chuck a DateDiff in here it’ll sort that out no problem”. This is entirely due to the ‘forgiving’ nature of the lounge towards my questions.

Thanks everybody

Ian

Reply | Quote

Since LastUpdated is a DAO property, you need to select Tools | References… in the Visual Basic Editor and set a reference to the Microsoft DAO 3.6 Object Library to “see” properties such as LastUpdated in the online help and in the Object Browser.

Reply | Quote

Hans Thanks

With a little bit of code this has proved to be a good first step solution, the next step is to make the first user update the tables in the back end, thus reducing the load on every on else’s Front End. I think this is going to be easy, once I’ve found a few bits of information.

Where would I be able to find the sort of information you just gave me? should i have used the Object Browser? I tried help and the MSKB but couldn’t find the LastUpdated property from table defs. If it’s the Object Browser I can help myself more in future.

Again, Thank You Hans.

Reply | Quote

In Access 2002, you can check the DateModified property of CurrentData.AllTables(“YourTableName”), but I think this property didn’t exist in Access 2000.

The DAO TableDef object has a property LastUpdated: CurrentDb.TableDefs(“YourTableName”).LastUpdated.

Reply | Quote