How to check for running instance of access database and then update (1 Viewer)

Voyager

Registered User.
Local time
Tomorrow, 01:48
Joined
Sep 7, 2017
Messages
95
Hi team,
I need your assistance in the below issue.
Requirement: I need to capture data in a database firstly code has to check if the specific database is already opened if database is opened then data has to be captured else it has to open the specific database and then capture the data.
Issue : Though code is working if I have some other database opened the code is looking for the table in the database which is already in open and shows error instead of opening the specific database. Could you assist.

Code:
Dim dB as dao.database
Dim rst as dao.recordset
Dim nwtsk as string
Dim dpth as string
Dim acapp as Object

dpth=“D:\dbtest\test_Database.mdb"”
On error resume next
Set accapp =getobject(“,access.application”)
Accapp.visible= true 
If (accapp is nothing) then
Set accapp =new access.application
End if
With accapp
.opendatabase dpth
.visible = true
End with

Set dB= current dB
Set rst= dB.openrecordset(“tstdata”)
With rst
.addnew
!nwtsk = TEST1

End Sub
 

MarkK

bit cruncher
Local time
Today, 13:18
Joined
Mar 17, 2004
Messages
8,178
Can you explain why it has to be done this way?
...firstly code has to check if the specific database is already opened if database is opened then data has to be captured else it has to open the specific database and then capture the data.
This seems like a very cumbersome approach, so before elaborating on how to make this work, maybe you can say more about why it is a good idea to make it work this way.
Why not just open the file you need directly? Why get code to find an instance and make it visible?
hth
Mark
 

Cronk

Registered User.
Local time
Tomorrow, 07:18
Joined
Jul 4, 2013
Messages
2,770
I agree with Mark - does it matter that the database is open. Just get the data directly, open or not, viz

Code:
set rst = currentdb.openrecordset("Select * from tstdata in "D:\dbtest\test_Database.mdb")
 

bastanu

AWF VIP
Local time
Today, 13:18
Joined
Apr 13, 2010
Messages
1,401
You can have a look at my website forestbyte.com, I have in the VBA Samples page a module (Switch between Access instances) by Graham Mandeno, Alpha Solutions, Auckland, NZ that you can download and import into your application.

Cheers,
Vlad
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 16:18
Joined
Feb 19, 2002
Messages
42,973
Add me to the puzzled column. Access databases are never opened hidden unless some other application is running OLE automation against them. Plus automation code would typically run against the FE rather than the BE so there won't be any data to "capture" that you couldn't get by linking directly to the BE.
 

Voyager

Registered User.
Local time
Tomorrow, 01:48
Joined
Sep 7, 2017
Messages
95
Firstly I have integrated outlook with ms office so that any mails in the outlook inbox will get downloaded into access if I click a button. Now such an integration is affected when I open some other database. The code checks for the table in the database which is kept opened first. If it is not doing it shows error.
 

Voyager

Registered User.
Local time
Tomorrow, 01:48
Joined
Sep 7, 2017
Messages
95
Is there anything that I need to do in
Set db = current db
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 04:18
Joined
May 7, 2009
Messages
19,169
Why do you need to test if
it is open or not, you will
open it anyway.

there is much better syntax
than opening the Other Db.
just make sure that the DB's
default Open Mode is Shared:

to insert record to Other DB:
Code:
Dim dpth As String
dpth = "D:\dbtest\test_Database.mdb"
CurrentDb.Execute "INSERT INTO tstData (nwtsk) IN '" & dpth & "' SELECT 'Test';"
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 15:18
Joined
Feb 28, 2001
Messages
27,001
Unless you have a security issue or a networking issue that isn't in evidence at the moment, I'm with the others who are puzzled by the convoluted process you describe.

WHY does this issue come up anyway? It almost sounds like you are incorrectly sharing a database - and by "incorrect" I mean "in the mechanical sense." What ARE these databases and why do they not already have links to each other if there is some data to be stored anyway?

Specifically, since Access in full-native mode uses SMB protocols and file-sharing methods to fetch/store data, having a link from DB "A" to DB "B" is passive unless you actually have to touch "B" from "A" anyway. If "A" is the active one, the host of "B" doesn't get involved except when there is a file-system "touch" and those only occur when the DB starts up (to verify data availability) and if you actually fetch or store something. At MOST, you will have a file handle open on "B" originating from the network connection leading back to "A" - and that's ALL.
 

static

Registered User.
Local time
Today, 20:18
Joined
Nov 2, 2015
Messages
823
GetObject allows you to specify the file path...

Code:
With GetObject("D:\dbtest\test_Database.mdb")
	.CurrentDb.Execute "insert into tstdata (nwtsk) values (" & sqlstr(test1) & ")"
End With

Code:
public function sqlstr(s) as string
	s=replace(s,"""","""""")
	s=replace(s,"'","''")
	sqlstr="'" & s & "'"
end function
 

static

Registered User.
Local time
Today, 20:18
Joined
Nov 2, 2015
Messages
823
I'm with the others who are puzzled by the convoluted process you describe.

WHY does this issue come up anyway?

My take was that he's running this from Outlook, not Access.

He's trying to open the file, but if he's got another database open it's returning that one which isn't the one he wants to update.
 

MarkK

bit cruncher
Local time
Today, 13:18
Joined
Mar 17, 2004
Messages
8,178
A few thoughts...

Apart from whether this is a good idea to do or not, you can find and kill processes in windows using code like...
Code:
  Set service = GetObject("winmgmts:{impersonationLevel=impersonate}!\\localhost\root\cimv2")
  Set procs = service.ExecQuery("SELECT * FROM Win32_Process WHERE Name = 'msaccess.exe'")
  For Each proc In procs
    proc.Terminate
  Next
This is from a .vbs script file, so the variables are not declared. Not sure what happens if you run this in Access VBA, since it will kill the process in which it is running. If that is the first process in the procs collection, it might not kill the remaining processes.


static, FWIW, I would do this ...
Code:
With GetObject("D:\dbtest\test_Database.mdb")
	.CurrentDb.Execute "insert into tstdata (nwtsk) values (" & sqlstr(test1) & ")"
End With

public function sqlstr(s) as string
	s=replace(s,"""","""""")
	s=replace(s,"'","''")
	sqlstr="'" & s & "'"
end function

like this...
Code:
Private Sub Test19234762830()
    Const SQL_INSERT As String = _
        "INSERT INTO tTestData As t " & _
            "( t.Name, t.Value ) " & _
        "VALUES " & _
            "( p0, p1 )"

    With OpenDatabase("D:\dbtest\test_Database.mdb") [COLOR="Green"]' gets DAO.Database without Access.Application[/COLOR]
        With .CreateQueryDef("", SQL_INSERT)         [COLOR="green"]' creates temp DAO.QueryDef[/COLOR]
            .Parameters(0) = "framing lumber order"
            .Parameters(1) = "2 pcs. of 8' 2""x4"""  [COLOR="Green"]' has various embedded quotes[/COLOR]
            .Execute [COLOR="Green"]' QueryDef succeeds, handles embedded delimiters automatically, without error[/COLOR]
            .Close
        End With
    End With
End Sub
...such that embedded delimiters are preserved, and no errors occur inserting them in the field.

hth
Mark
 

Users who are viewing this thread

Top Bottom