Select Case - SQL

[auujxa2 via AccessMonster.com]

We have different networks for different divisions, and since I can't predict
the division, I'm using a select case method. I only entered 1 case. The
case is the path the current user is in.

The query runs fine. But it's updating the current database, not the "Run
Tracker" database. I need to shift the focus and define the objACC database
for the SQL code. I tried "With", that didn't work.

Please help. Thank you in advance.

Dim strSQL As String
Dim CurrentUser As String
Dim strProjectPath As String
Dim objACC As New Access.Application

strProjectPath = Application.CurrentProject.Path

CurrentUser = Environ("UserName")

DoCmd.SetWarnings False

Select Case strProjectPath

Case ("\\s914pub\Vendor Matrix - BSS")

Set objACC = GetObject("\\s914pub\Run Tracker\Run Tracker.
mdb")

strSQL = "INSERT INTO MasterTbl ( When, Who, What)" & _
" SELECT Now()" & "," & " """ & CurrentUser & ""","
& " " & "'A1'" & " AS Str"

DoCmd.RunSQL strSQL

strSQL = " UPDATE [MasterTbl] SET [Path] = Application.
CurrentProject.Path" & _
" WHERE [Path] is Null"

DoCmd.RunSQL strSQL

objACC.Quit

End Select

 

[Michel Walsh]

The Connect property gives the intended mdb:

? CurrentDb.TableDefs("YourLinkedTableNameHere").Connect
\\s914pub\Run Tracker\Run Tracker.mdb



If you want to change it, specify the Connect property with the new
location, and THEN, refreshLink:

CurrentDb.TableDefs("YourLinkedTableNameHere").RefreshLink



You can store the various paths in a local table, two fields:

shortName, fullPath '
fields name
"\\s914pub\Vendor Matrix - BSS" " \\s914pub\Run Tracker\Run Tracker.mdb"
... data sample


and make a DLookup on the short name, to get the full path (rather than
using a hard coded Select-Case). You can so add/modify the related tables
without touching the code itself, much much more secure, safe, or
maintanable.



Vanderghast, Access MVP

 

[auujxa2 via AccessMonster.com]

I figured it out.

I just simply put objACC in front of the docmd.runSQL

thank you for taking the time to help me

The Connect property gives the intended mdb:

? CurrentDb.TableDefs("YourLinkedTableNameHere").Connect
\\s914pub\Run Tracker\Run Tracker.mdb

If you want to change it, specify the Connect property with the new
location, and THEN, refreshLink:

CurrentDb.TableDefs("YourLinkedTableNameHere").RefreshLink

You can store the various paths in a local table, two fields:

shortName, fullPath '
fields name
"\\s914pub\Vendor Matrix - BSS" " \\s914pub\Run Tracker\Run Tracker.mdb"
... data sample

and make a DLookup on the short name, to get the full path (rather than
using a hard coded Select-Case). You can so add/modify the related tables
without touching the code itself, much much more secure, safe, or
maintanable.

Vanderghast, Access MVP

We have different networks for different divisions, and since I can't
predict

[quoted text clipped - 42 lines]

End Select