Visual Basic and Access

stakey

Hi Folks,

Haven't done any VB in about 2-3 years, was just writing a small program in the last hour which has caused me quite a few problems.

I am just doing a test connection and pulling data from a database into a datagrid....

i am using the Microsoft Jet Driver to connect to the database.

Problem?

From what i can see the db connection goes ahead fine, but i can pull anything out of the database.... could someone have a quick look...

here's the code

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset

Private Sub cmdCloseCon_Click()

End

End Sub

Private Sub dataPull_Click()


Dim sql As String

sql = "Select * FROM user"

Set rs = New ADODB.Recordset

rs.Open sql, cn

Set dgResults.DataSource = rs

Set rs = Nothing

cn.Close





End Sub

Private Sub Command2_Click()

Dim db As Database, rs As Recordset
Set db = DBEngine.OpenDatabase("C:\Documents and Settings\Brian\Desktop\Liams Program\db1.mdb")
Set rs = db.OpenRecordset("user")
Set Data1.Recordset = rs

End Sub

Private Sub Form_Load()
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; data source = C:\Documents and Settings\Brian\Desktop\Liams Program\db1.mdb; JET OLEDB:Database password = 1234567"
End Sub

bonkey

Don't backslashes have to be escaped (doubled) inside a string (i.e. the Form_Load and Command2_click code doesn't specify the correct path.)

jc

JohnnyBravo

yup

stakey

As in

Private Sub Command2_Click()

Dim db As Database, rs As Recordset
Set db = DBEngine.OpenDatabase("C:\\Documents and Settings\\Brian\Desktop\\Liams Program\\db1.mdb")
Set rs = db.OpenRecordset("user")
Set Data1.Recordset = rs

End Sub

Private Sub Form_Load()
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; data source = C:\\Documents and Settings\\Brian\\Desktop\\Liams Program\\db1.mdb; JET OLEDB:Database password = 1234567"
End Sub

--- when i do this it claims there is a problem with the password, but this is the set password for the database....

bonkey

Connection Strings are finicky little bastids, ain't they.

My top culprits for your latest problem are :


1) Connect Strings are normally case sensitive. If the listed opton is "Database Password", then "Database password" probably won't get picked up.

2) They're often space-sensitive. F'rexample...if you're expected to give "Username=fred;Password=foo", then having any spaces in there could shag you (e.g. 'Username =fred', or 'Username= fred', or "...; Password=", or anything).

Also, your queries etc. are being opened from teh Connection object which is from ADO.Net. The DBEngine code is from DAO, yes? Note that you only set the password on the Connection object, and not on the DBEngine call....so if the database is passworded, you'll run into trouble.

Unless there's some reason for not doing it, get rid of one of the data-access technologies before you go any further.

jc