Linked table being deleted and created locally?? (1 Viewer)

[sammylou]

I have just split my multi-user DB into Front end and back end. All appears fine apart from there is code in the autoexec module that checks to see if a particular table has been updated today. Basically the first person to login to the DB each day will run this procedure without realising. Now I have FE & BE however, because the query used by the autoexec module is a "Make table" query rather than delete all data and use an append query, every person logging in is running the update each day if it is the first time they have logged in on their PC and they are getting a local table on each of their computers. Is there any code that will create the table in the networked location (when the first person of the day logs in), rather than me having to change all my "delete table" queries to cleardown data and then re-append updated data?

There are quite a lot of delete queries in my DB and I am now worried that I shouldn't have split the DB as I may have caused myself loads of work which I don't have time to do.

I have looked on other threads but cannot find the answer and would greatly appreciate some help

 

[Pat Hartman]

Lookup the Insert Into statement in help. If you can't find it by searching, open the table of contents and drill down into the Jet SQL Reference section. It will give you the syntax for using an "IN" clause to refer to a different database.

 

[sammylou]

thanks for your help. Unfortunately I cannot locate this Help file in Access, I have even expanded all of the Help files.

Additionally I now have a problem whereby one member of staff who uses a laptop to connect to the network drive, cannot use the same FE as everyone else. This is because the networked drive has a different name on her PC. I have tried 3 different ways of creating a unique FE for her. If I try and change the linked tables when at her PC using her FE, it wants me to point to the correct path for each table in the DB and there are 100+ of them. I cannot find a way of selecting all of them and pointing at the right directory once, it asks me everytime for every table!

If I create a new FE for her and import everything (except linked tables) from the generic FE, and then use the Linked table manager to create the linked tables then it doesn't compile properly, even though I have double checked the VB references to make sure they are the same?

I am now about to give up and tell her she cannot have access to the DB. Please help. Thanks

 

[Pat Hartman]

thanks for your help. Unfortunately I cannot locate this Help file in Access, I have even expanded all of the Help files.

Turn off the office assistant if that is what is getting in the way. Open Help and right under the search box is the link to the table of contents. Somewhere near the bottom of the list is "Microsoft Jet SQL Reference". Drill down in there to find the Insert syntax. I would post it for you but this is an important part of help that you need to know how to find.

As to relinking tables, when you use the linked tables manager, there is a box at the bottom left that says something like "always prompt for new location". Check that and choose the select all option. That way you only have to select the new location once rather than for each table.

 

[sammylou]

Again thanks for your help, however I must have some help files missing because I have looked under "Creating and working with Databases and Objects folder" but I don't have "Database objects" + "Queries" + "Microsoft Jet SQL Reference" folder??

I have also tryed searching by using a Jet SQL keyword such as "WHERE" but this brings back nothing.

Do I need the systems people to add some missing help files or can I download this myself? The problem here is that the people who look after the PCs and software admit that they know nothing about Access!!

I am using Access 2002 SP3

 

[Pat Hartman]

It is possible that the help files were not installed when Access was installed since in some versions they are optional. Get your support staff to install them if necessary. Get all the other help files for other Office apps while you're at it.

However, I don't think you followed my directions. I told you to open the table of contents and near the bottom of the list you will find the entry I am talking about.

 

[sammylou]

I am assuming you mean the tab page "contents" under help? If this is correct then the topic is definately not under there.

If "Table of contents" is something else than the Help menu at the top, then I have no idea where it is, I have looked under all the menus and cannot see anything called this

 

[Pat Hartman]

I uploaded three pictures:
ACHelp2 is A97 (I think). You can't see the entry because it is cut off. But it is near the bottom of the list.
ACHelp3 is A2003 and the entry is "Table of Contents" - when you expand it, the entry is near the bottom.
ACHelp is A2002/A2000 and the entry is 4 up from the bottom of the list

If you are not seeing Microsoft Jet SQL Reference, you'll need to load it. I think it loads with office if you select all the help files.

The actual help file name is - JETSQL40.CHM - for Jet 4.0. The numbers will be different if you are using an earlier version of Jet. Search your C: drive for *.chm to bring up a list of all the help files and find the Jet help file that way. If push comes to shove, you can open these files directly. A2K was especially poor with regards to how it handled help. I keep a folder on my desktop with shortcuts to a bunch of help files so I can get to them easily.

 

[sammylou]

Thank You very much for all your help. I can now see that I have 3 help files missing including the JET SQL reference one, although I have now located the .chm file on my C:\drive.

I am assuming now though that I need to replace all my "Make Table" Queries in the FE DB with modules showing "SELECT...INTO statements" It might be quicker to just delete the data in each table first and change the Make Table" queries to "Append" queries?

 

[Pat Hartman]

I would use a delete query followed by an append query rather than deleting a table object. Although in terms of database bloat both are bad. If these tables have more than a few records, you may need to compact this database every day to get rid of bloat.

You never said what version of Access you are working with. I had serious problems with missing help files with A2K which is why I resorted to the folder of help files on my desktop. But in all versions of Access, getting SQL, DAO, and ADO help is sketchy at best.

 

[sammylou]

I am using Access 2002. Thanks again for all your help. My systems people have now put the missing help files in place!

 

[Pat Hartman]

Hooray!!!!