Referencing DESKTOP Database Connect String

[PSUIVERSON]

I have a program that pulls information from tables in MS ACCESS.

The database I want to reference is on the DESKTOP of each users local machine.

Here is the code:


' Build connection string
connectString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Right here I would put the database path but it will not work for a desktop reference"

adoConnection.Open connectString


It works if I copy the database to the root directory of my C: and reference it like this:


' Build connection string
connectString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:/sqlSOM.mdb"

adoConnection.Open connectString


How do I do SOURCE = to point at a desktop database?

Thanks in advance...

 

[Swi]

Try something like this:

Private Sub Command1_Click()
Dim WSHShell As Object
Dim Conn As ADODB.Connection
Dim Rs As ADODB.Recordset

Set WSHShell = CreateObject("WScript.Shell")
Set Conn = New ADODB.Connection
Set Rs = New ADODB.Recordset

Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
WSHShell.SpecialFolders("Desktop") & "\UsersDB.mdb"

Rs.Open "SELECT * FROM Users", Conn, adOpenForwardOnly, adLockReadOnly

Do Until Rs.EOF
MsgBox Rs.Fields(0).Value
Rs.MoveNext
Loop

Rs.Close
Conn.Close
Set Rs = Nothing
Set Conn = Nothing
Set WSHShell = Nothing
End Sub

Swi

 

[JCruz063]

PSUIVERSON,
It doesn't sound wise to have the database of an application sit right on the Desktop of each user's machines as that would make the database too vulnerable. For instance, if a user happens to rename, delete, or move the database, youre application will stop functioning (or at least, it won't be able to access the database).

You should place the database in the same folder where the application is and refer to it using the App object in Visual Basic. Like this:

connectString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & [b]App.Path & "\sqlSOM.mdb"[/b]

That's guaranteed to work. If you still want the database visible on the user's desktop, simply add a shortcut to it.

One last point: In the event that your application runs on a different mahine than the current user, then create a folder (perhaps inside the Program Files folder of your user's computer) named after the application and place the database there. In code, either create a constant that has the path to the database or have the user himself enter the path. Having a constant is good because if you happen to change the location of the database, you will only have to modify the program at one place, the constant. Having the user enter the path would be even better, though, as no code changes would be required.

JC

_________________________________________________
To get the best response to a question, read faq222-2244!

 

[PSUIVERSON]

The database is actually just a GUI tool not the actual DATABASE. Sorry for the confusion. Its a SQL database with VIEWS based on the user rights. They only have read only rights.

Each of them ODBC into what they are allowed to see and the application runs off of the linked tables.

I supposed what I can do is keep two versions of the GUI Access db / one in the same folder as the APP to do the referencing and one on the desktop for their general querying etc.

Thanks...

 

[bjd4jc]

Or you can do as JCruz suggested and create a Shortcut on the user desktop instead of having the actual file sitting there.

 

[PSUIVERSON]

Each user will have different views and therefore not sharing the same ACCESS front end.

 

[JCruz063]

I supposed what I can do is keep two versions of the GUI Access db / one in the same folder as the APP to do the referencing and one on the desktop for their general querying etc.

I don't quite understand what you mean by that, but if you mean that you will have a shortcut on the Desktop of each user, then it should be fine. I understand that users have Read-Only rights but you shouldn't duplicate the data just to allow them to see it. Like I said before, I believe you should have database on the same folder as the application and simply add a shortcut to the same database file on the desktop.


JC

_________________________________________________
To get the best response to a question, read faq222-2244!

 

[PSUIVERSON]

Here's the layout

1 - SQL DATABASE
2 - Access Front End (ODBC to SQL)
3 - Access Font End resides on the DESKTOP of each user
4 - Only See the VIEWS they are allowed to see
5 - VB Application Supported by their VIEW rights from the desktop front end and the tables that reside there

This may be a moot point. I am waiting for the SQL DB to be completed and am going to reference that for my tables in the application. We'll see how it works.

 

[petermeachem]

Can't see the point in having the vb prog talk to some local Access file instead of sql server. That would seem to be the worst possible way of arranging it.

http://www.accuflight.com

http://www.lettersdirect.co.uk

http://www.ourhouseinportugal.co.uk/

 

[bjd4jc]

Is this a true access database or is it an access data project linked to SQL? I still don't see why you can use Security Settings in SQL Server to restrict what views each user is allow to see. Then you wouldn't have to go through the trouble of creating a separate database for each user's desktop.... Or maybe I am still missing something here.

 

[PSUIVERSON]

Agreed. Thanks for making me think about this. Until now there was not a sql db to work off of.

It was dBase files that were imported into an ACCESS db that supported the application. Now we are putting it into a sql db and updating each month.

I'll wait until the sql db is setup and then reference that.

Thanks.