Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Access Database
So I can access the Access Database using the following:
Dim myDB As ADODB.Connection Dim myRS As ADODB.Recordset Set myDB = New ADODB.Connection Set myRS = New ADODB.Recordset myDB.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=M:\Work Log\Work Log .mdb;USER ID=Facilities;Password=;" myRS.Open "SELECT DISTINCT `Jamis #` FROM `Work Log` `Work Log` WHERE status < 'Complete' or status < 'Canceled' or status < 'On Hold'", myDB, adOpenDynamic, adLockReadOnly and it works fine if nobody has the database open, however if someone has the database open, I get an error. Anyway around this? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Access Database
I presume you have the default open mode set to 'Shared'........
Given that, and that your database is user-level secured, have you thought of setting up another account and password for secured 'read-only' access to the database? Incidentally, why don't you have to reference your workgroup information file to access your database? Regards, VBA Dabbler " wrote: So I can access the Access Database using the following: Dim myDB As ADODB.Connection Dim myRS As ADODB.Recordset Set myDB = New ADODB.Connection Set myRS = New ADODB.Recordset myDB.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=M:\Work Log\Work Log .mdb;USER ID=Facilities;Password=;" myRS.Open "SELECT DISTINCT `Jamis #` FROM `Work Log` `Work Log` WHERE status < 'Complete' or status < 'Canceled' or status < 'On Hold'", myDB, adOpenDynamic, adLockReadOnly and it works fine if nobody has the database open, however if someone has the database open, I get an error. Anyway around this? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Access Database
In the grander scheme of things of things no one should have the database
open. If at all possible set up the database with a front end/back end architecture. The back end is all of the tables. The front end is all of the forms queries and reports. The front end is linked to the back end via links (Get External Data - Link Data). This way no one will have the database exclusively open. There is also the added benfit of having multiple front ends for one back end. HTH " wrote: So I can access the Access Database using the following: Dim myDB As ADODB.Connection Dim myRS As ADODB.Recordset Set myDB = New ADODB.Connection Set myRS = New ADODB.Recordset myDB.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=M:\Work Log\Work Log .mdb;USER ID=Facilities;Password=;" myRS.Open "SELECT DISTINCT `Jamis #` FROM `Work Log` `Work Log` WHERE status < 'Complete' or status < 'Canceled' or status < 'On Hold'", myDB, adOpenDynamic, adLockReadOnly and it works fine if nobody has the database open, however if someone has the database open, I get an error. Anyway around this? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Update Excel Database via Access Main Database with a script | New Users to Excel | |||
Access Database | Excel Discussion (Misc queries) | |||
users and DAO with access database | Excel Programming | |||
VBA Database access - How? | Excel Programming | |||
VBA Database access | Excel Programming |