Connection String with Access Workgroup Info (mdw)

[Duppypog]

I've searched through several forums without finding a definitive answer to
this question:

What is the proper syntax for a connection string that is connecting to an
Access 97 db with workgroup security applied?

I tried the following. I don't get an error, but it also displays NO
results:

Public Shared ReadOnly OleDbConnectionString As String =
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=DBQ=c:\CORmandOTweb\NewSecure
CORMandatoryOTList.mdb
SystemDB=c:\inetpub\wwwroot\aspdotnet\cormandot\db\corrections.mdw;UID=usern
ame;PWD=anything"

Also tried:

Dim cnn As OleDbConnection = New
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Persist Security
Info=false;Data Source=" &
System.Web.HttpContext.Current.Server.MapPath("/CORmandOTweb/NewSecure
CORMandtoryOTList.mdb") & "; Jet OLEDB:System
Database=c:\inetpub\wwwroot\aspdotnet\cormandot\db\corrections.mdw;UserID=us
ername;Password=anything")


All help will be greatly appreciated!
Lynnette

 

[Duppypog]

Andy, Thanks. I had to change line 3 to "Data Source=" instead of just
"Source=". I need to change the data source to point to a virtual
directory. I tried this code and it returned no error, but no data either.
Can you (or anyone) spot the problem?

Dim cnn As OleDbConnection = New
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; " & _

"Data Source=" &
System.Web.HttpContext.Current.Server.MapPath("/CORmandOTweb/NewSecure
CORMandtoryOTList.mdb") & "; " & _

"Jet OLEDB:System
Database=c:\inetpub\wwwroot\aspdotnet\cormandot\db\corrections.mdw; " & _

"User id=dbadmin; Password=jaxkdbadmin;")


Thanks again,
Lynnette

 

[Duppypog]

Thanks for all your help. I will post the procedure for your review.
Lynnette


This code works:
Private Sub Datafill()
Dim da As OleDbDataAdapter

Dim ds As DataSet

Dim strSQL As String

Select Case Me.rblView.SelectedItem.Value

Case "1st Shift"

strSQL = "Select VolunteerID, Name, SquadODG, LastWorked, exempt, HireDate,
shift, LOC_NAME From tblVolunteer_list WHERE shift='1' ORDER BY exempt DESC,
LastWorked ASC, HireDate DESC"

Case "2nd Shift"

strSQL = "Select VolunteerID, Name, SquadODG, LastWorked, exempt, HireDate,
shift, LOC_NAME From tblVolunteer_list WHERE shift='2' ORDer BY exempt DESC,
LastWorked ASC, HireDate DESC"

Case "3rd Shift"

strSQL = "Select VolunteerID, Name, SquadODG, LastWorked, exempt, HireDate,
shift, LOC_NAME From tblVolunteer_list WHERE shift='3' ORDER BY exempt DESC,
LastWorked ASC, HireDate DESC"

End Select

Dim cnn As OleDbConnection = New
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; " & _

"Data Source=C:\Inetpub\wwwroot\ASPdotNET\CORmandOT\db\NewSecure
CORMandatoryOTList.mdb; " & _

"Jet OLEDB:System
Database=c:\inetpub\wwwroot\aspdotnet\cormandot\db\corrections.mdw; " & _

"User id=dbadmin; Password=jaxkdbadmin;")

Try

cnn.Open()

da = New OleDbDataAdapter(strSQL, cnn)

ds = New DataSet()

da.Fill(ds, "tblVolunteer_list")

Session("DataView") = ds.Tables("tblVolunteer_list").DefaultView()

Catch

'lblError.Text = e.ToString

Finally

cnn.Close()

cnn.Dispose()

End Try

End Sub

This code does NOT work (but I don't know why):
Private Sub Datafill()

Dim da As OleDbDataAdapter

Dim ds As DataSet

Dim strSQL As String

Select Case Me.rblView.SelectedItem.Value

Case "1st Shift"

strSQL = "Select VolunteerID, Name, SquadODG, LastWorked, exempt, HireDate,
shift, LOC_NAME From tblVolunteer_list WHERE shift='1' ORDER BY exempt DESC,
LastWorked ASC, HireDate DESC"

Case "2nd Shift"

strSQL = "Select VolunteerID, Name, SquadODG, LastWorked, exempt, HireDate,
shift, LOC_NAME From tblVolunteer_list WHERE shift='2' ORDer BY exempt DESC,
LastWorked ASC, HireDate DESC"

Case "3rd Shift"

strSQL = "Select VolunteerID, Name, SquadODG, LastWorked, exempt, HireDate,
shift, LOC_NAME From tblVolunteer_list WHERE shift='3' ORDER BY exempt DESC,
LastWorked ASC, HireDate DESC"

End Select

Dim DataPath As String =
System.Web.HttpContext.Current.Server.MapPath("NewSecure
CORMandatoryOTList.mdb") 'also tried "/CORmandOT/NewSecure
CORMandatoryOTList.mdb"

Dim cnn As OleDbConnection = New
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; " & _

"Data Source=" & DataPath & "; " & _

"Jet OLEDB:System
Database=c:\inetpub\wwwroot\aspdotnet\cormandot\db\corrections.mdw; " & _

"User id=dbadmin; Password=jaxkdbadmin;")

Try

cnn.Open()

da = New OleDbDataAdapter(strSQL, cnn)

ds = New DataSet()

da.Fill(ds, "tblVolunteer_list")

Session("DataView") = ds.Tables("tblVolunteer_list").DefaultView()

Catch

'lblError.Text = e.ToString

Finally

cnn.Close()

cnn.Dispose()

End Try

End Sub