Network interrupted on Compact Database
- Thread starter CedarTree
- Start date
- Local time
- Today, 06:34
- Joined
- Feb 28, 2001
- Messages
- 30,563
The FASTEST way to do this is to create an empty database that will be the template of the file you will actually use, all tables defined but empty. Also, this empty file can already have been compacted and repaired; i.e. good to go as soon as you make the copy.
Put this template tertiary DB in the same place you keep the BE file. Using the File System Object you can do a FileCopy of the template database to the place where you will use it, which probably would be the place where your FE file is located. Relink the tables in that copy so that even though in an external file, they will be visible to your FE.
https://access-programmers.co.uk/forums/showthread.php?t=204413
Now, the answer to your question: how can I edit the sql of a query in another database?
Answer: Once the tables are linked to your FE, you DON'T edit the SQL of a query in another database. You can edit a query locally defined in your FE - because the table definitions are now visible to your FE. You can use explicit DoCmd.RunSQL on SQL query strings. You DID say you were editing the SQL, right? I inferred that you are generating your SQL based on some common parts and some variable parts. If so, you just use the names of the now-linked tables just as though they had been in your FE all along.
When you are done, though, there is one more bit of overhead. In post #5 of the link, there is an example of deleting a table definition. You would delete this in your own database via CurrentDB rather than through some type of database object. After all, you created the linked TableDef entries in your FE DB earlier, so you can delete them now. OK... if you want to quibble, CurrentDB IS a database object. But you don't have to create a separate object.
Note that you technically didn't open the external database if you followed the ideas of the article in that link. Instead you did a .RefreshLink, which means you didn't do an explicit OpenDatabase and therefore don't have to close the external database. You just have to unlink it, which you can do by deleting the table definition. If you are truly done with the file (i.e. all temp tables unlinked), you can also use the File System Object one more time to delete that copy of the database you made earlier.
This is an overview and I strongly recommend you read up on the relevant methods of the File System Object and the VBA code for table relinking. Do a little on-line searching in order to get more comfortable with the routines.
Put this template tertiary DB in the same place you keep the BE file. Using the File System Object you can do a FileCopy of the template database to the place where you will use it, which probably would be the place where your FE file is located. Relink the tables in that copy so that even though in an external file, they will be visible to your FE.
https://access-programmers.co.uk/forums/showthread.php?t=204413
Now, the answer to your question: how can I edit the sql of a query in another database?
Answer: Once the tables are linked to your FE, you DON'T edit the SQL of a query in another database. You can edit a query locally defined in your FE - because the table definitions are now visible to your FE. You can use explicit DoCmd.RunSQL on SQL query strings. You DID say you were editing the SQL, right? I inferred that you are generating your SQL based on some common parts and some variable parts. If so, you just use the names of the now-linked tables just as though they had been in your FE all along.
When you are done, though, there is one more bit of overhead. In post #5 of the link, there is an example of deleting a table definition. You would delete this in your own database via CurrentDB rather than through some type of database object. After all, you created the linked TableDef entries in your FE DB earlier, so you can delete them now. OK... if you want to quibble, CurrentDB IS a database object. But you don't have to create a separate object.
Note that you technically didn't open the external database if you followed the ideas of the article in that link. Instead you did a .RefreshLink, which means you didn't do an explicit OpenDatabase and therefore don't have to close the external database. You just have to unlink it, which you can do by deleting the table definition. If you are truly done with the file (i.e. all temp tables unlinked), you can also use the File System Object one more time to delete that copy of the database you made earlier.
This is an overview and I strongly recommend you read up on the relevant methods of the File System Object and the VBA code for table relinking. Do a little on-line searching in order to get more comfortable with the routines.
Okay one last question (I think) for now...
Trying to link to the temporary database. I can use docmd.xfer acLink, etc. but that makes the navigation pane behave in ways I don't want it to.
So I thought let me try to link via "OCDB" from one Access database to another. I've tried all kinds of connection strings but I keep getting an error with a missing installable ISAM. What connection string should I used? Thanks!
Trying to link to the temporary database. I can use docmd.xfer acLink, etc. but that makes the navigation pane behave in ways I don't want it to.
So I thought let me try to link via "OCDB" from one Access database to another. I've tried all kinds of connection strings but I keep getting an error with a missing installable ISAM. What connection string should I used? Thanks!
moke123
AWF VIP
- Local time
- Today, 07:34
- Joined
- Jan 11, 2013
- Messages
- 4,753
What is it doing?
assuming its showing I think there are 2 ways to hide it. You can run the db as an accdr or use this code
Code:
DoCmd.SelectObject acTable, "OneOfYourTableNames", True
DoCmd.RunCommand acCmdWindowHide- Local time
- Today, 06:34
- Joined
- Feb 28, 2001
- Messages
- 30,563
If you try to use ODBC, then yes, you DO need a driver. The only way you CAN do a link without a driver is Access-to-Access.
Here is a link to how you create a table definition (TableDef) object and append fields to it. Tedious, but it is a "code it once as a sub and call it when you need it" situation.
https://msdn.microsoft.com/en-us/library/office/ff835094.aspx
But there IS the chance you could just "import" the table definition.
https://www.devhut.net/2010/06/10/ms-access-vba-import-external-database-tables/
So copy the file, import the tables so you have a local definition, then use them like they were defined locally.
Here is a link to how you create a table definition (TableDef) object and append fields to it. Tedious, but it is a "code it once as a sub and call it when you need it" situation.
https://msdn.microsoft.com/en-us/library/office/ff835094.aspx
But there IS the chance you could just "import" the table definition.
https://www.devhut.net/2010/06/10/ms-access-vba-import-external-database-tables/
So copy the file, import the tables so you have a local definition, then use them like they were defined locally.
Similar threads
