This is my first post. Tried to explain everything as clear as I could.
I have 3 MS Access databases:
- Database1 > contains only one table called "sample"
- Database2 > is filled with multiple stored procedured that are about to be executed after table import
- Database3 > is NOT related to any of this but it causes the problem. This db should be opened whole time (Access GUI)
I want to create linked table in Database2 (this one with stored procedures) that points to table "sample" in Database1. Im using python pywin32 to accomplish this. My custom function "linker" works BUT in case when ALL 3 databases are CLOSED. As mentioned earlier Database3 (which is not realated to this whole procedure) should be always open because end user is constantly reading, copying or modyfing data.
Despite that Database3 is not related (not in code) when it is open (through Access GUI) and i run linker function i will get an error (transated):
pywintypes.com_error: (-2147352567, 'Exception occured.', (0, None, 'Database is already open.', None, -1, -2146820421), None)
objAccess.OpenCurrentDatabase("absolute\path\to\Database2") is pointing explicitly to Database2 but for some reason pywin32 is not going to redirect connection to Database2 and still points to Database3 (because it was open when function started)
When i use try-except block, exception gets caught and function procedes further creating linked table but because pywin32 is holding connection to wrong database, linked table is also stored in wrong place (in Database3 instead of Database2)
This is my code:
import win32com.client import pywintypes def linker(source_database: str, source_name: str, dest_name: str) -> None: objAccess = win32com.client.Dispatch("Access.Application") try: objAccess.OpenCurrentDatabase("absolute\path\to\Database2") objAccess.Visible = False except pywintypes.com_error as e: print(e) # https://learn.microsoft.com/en-us/office/vba/api/access.docmd.transferdatabase try: objAccess.DoCmd.TransferDatabase( 2, # TransferType:=acLink = 2 "Microsoft Access", source_database, 0, # ObjectType:=acTable = 0 source_name, dest_name, ) except pywintypes.com_error as e: print(e) objAccess.CloseCurrentDatabase linker(Database1, oldName, newName)
To sum up. Is there a way to redirect connection excplicitly to Database2 (when Database3 is still open) so that linked table is stored in correct place?
At this point I can make it work by closing Access application at the very start of the function, but end user won't accept this solution in a long-run. Therefore I`m looking for a solution that will bypass closing Database3.