I Don't want the "CurrentDB"

[DougAtAvalon]

The workspaces code gets an error
How do a reference an external database?

**********************
Public Function GetSecurityOnOff(vNetworkPath)
GetSecurityOnOff = "Off"

Dim vSecurityValue As Integer
Dim dbs As Object
Dim vRecordset As Object

Set vRecordset = DBEngine.Workspaces(0).Databases(vNetworkPath)


SQLstring = "SELECT DefaultSettings.*, DefaultSettings.DefaultID FROM DefaultSettings WHERE DefaultID = 'SecurityOn'"

Set vRecordset = dbs.OpenRecordset(SQLstring)
With vRecordset
vSecurityValue = ![DefaultValue]
End With

If vSecurityValue = 0 Then GetSecurityOnOff = "Off"
If vSecurityValue <> 0 Then GetSecurityOnOff = &quot;On&quot;

Set vRecordset = Nothing
Set dbs = Nothing
End Function

 

[jimmythegeek]

Try something like this (I don't know if the syntax is perfect, but it's close):

===============
Public Function GetSecurityOnOff(vNetworkPath)
Dim strSQL As String
Dim ws As Workspace, db As Database, rs As Recordset

Set ws = CreateWorkspace(&quot;&quot;, &quot;admin&quot
Set db = ws.OpenDatabase(vNetworkPath)

strSQL = &quot;SELECT * FROM DefaultSettings WHERE DefaultID = 'SecurityOn'&quot;

Set rs = db.OpenRecordset(strSQL)

If rs.RecordCount > 0 Then
If rs![DefaultValue] = 0 Then
GetSecurityOnOff = &quot;Off&quot;
Else
GetSecurityOnOff = &quot;On&quot;
End If
Else
GetSecurityOnOff = &quot;Off&quot;
End If

rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
ws.Close
Set ws = Nothing
End Function
===============

It would be better to make the &quot;DefaultValue&quot; field a boolean (yes/no), then you could do this:

===============
Public Function GetSecurityOnOff(vNetworkPath) As Boolean
Dim strSQL As String
Dim ws As Workspace, db As Database, rs As Recordset

Set ws = CreateWorkspace(&quot;&quot;, &quot;admin&quot
Set db = ws.OpenDatabase(vNetworkPath)

strSQL = &quot;SELECT * FROM DefaultSettings WHERE DefaultID = 'SecurityOn'&quot;

Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

If rs.RecordCount > 0 Then
GetSecurityOnOff = rs![DefaultValue]
End If

rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
ws.Close
Set ws = Nothing
End Function
=============== Jim Lunde
compugeeks@hotmail.com
Custom Application Development

 

[RickSpr]

You don't necessarily need to create a second workspace to open an additional database.
Dim ws As Workspace
Set ws = DBEngine(0).Workspaces(0)
Set db = ws.OpenDatabase(vNetworkPath)

However, if the current user doesn't have access rights to the external database, creating a second workspace allows you to log in using a hard-coded user name and password. Rick Sprague

 

[DougAtAvalon]

JimmytheGeek,

First of all I'm sure you're not a geek.
When I tried your code I got &quot;Argument not optional at the CreateWorkspace line:

Set ws = CreateWorkspace(&quot;&quot;, &quot;admin&quot

RickSpr,

 

[DougAtAvalon]

jimmy,

with the code you gave me I get an error &quot;Argument not optional&quot; at the &quot;Create WorkSpace&quot; code:

********
Public Function GetSecurityOnOff(vNetworkPath)
Dim strSQL As String
Dim ws As Workspace, db As Database, rs As Recordset

Set ws = CreateWorkspace(&quot;&quot;, &quot;admin&quot
Set db = ws.OpenDatabase(vNetworkPath)

strSQL = &quot;SELECT * FROM DefaultSettings WHERE DefaultID = 'SecurityOn'&quot;

Set rs = db.OpenRecordset(strSQL)
************

-Doug

 

[DougAtAvalon]

Rick,

with your code I get an error &quot;Method or data member not found&quot; at line with &quot;.Workspaces&quot;

*************
Dim strSQL As String
Dim ws As Workspace, db As Database, rs As Recordset

Set ws = DBEngine(0).Workspaces(0)
Set db = ws.OpenDatabase(vNetworkPath)



strSQL = &quot;SELECT * FROM DefaultSettings WHERE DefaultID = 'SecurityOn'&quot;
************

-Doug

 

[RickSpr]

I knew I had miskeyed that somewhere!

That should have been:
DBEngine.Workspaces(0)

I was writing similar code later last night, and remembered I had messed it up by coding DBEngine(0).Workspaces(0) somewhere, but I didn't know where. Sorry about that. Rick Sprague

 

[jimmythegeek]

Use Rick's method, it is better than creating another workspace. Jim Lunde
compugeeks@hotmail.com
Custom Application Development

 

[DougAtAvalon]

Rick and Jimmy,

Thanks for your help and time.

-Doug

 

[DougAtAvalon]

Hey guys would you believe I've got one more question

thanks to you the code works, I have I button that writes to the file, but I get a pop-up screen to choose a type of database file (fox pro, etc.) I also tried to put in dbdrivernoprompt as a &quot;connection&quot;. Anyone know?

*********************
Private Sub BSecurityOnOff_Click()
Dim strSQL As String
Dim ws As Workspace, db As Database, rs As Object

Set ws = DBEngine.Workspaces(0)
Set db = ws.OpenDatabase(vNetworkPath)

strSQL = &quot;SELECT DefaultSettings.*, DefaultSettings.DefaultID FROM DefaultSettings WHERE DefaultID = 'SecurityOn'&quot;

Set rs = db.OpenRecordset(strSQL)
rs.Edit
If rs![DefaultValue] = 0 Then vRecordset!DefaultID = -1
If rs![DefaultValue] = -1 Then vRecordset!DefaultID = 0

rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
ws.Close
Set ws = Nothing

SecurityRedefined
End Sub