linking to an access database

[jamesoc]

could any body tell me whats wrong with ths code as i am stumped.any help would be great





Private Sub Form_Load()
Dim dbMyDatabase As Database
Set dbMyDatabase = OpenDatabase("MyDatabase.mdb")
Dim rsMyRS As Recordset

Set rsMyRS = dbMyDatabase.OpenRecordSet("MyTable", dbOpenDynaset)


End Sub









Private Sub lstRecord_Click()


Dim dbMyDB As Database
Dim rsMyRS As Recordset

Private Sub Form_Load()

Set dbMyDB = OpenDatabase("MyDatabase.mdb")
Set rsMyRS = dbMyDB.OpenRecordSet("MyTable", dbOpenDynaset)

If Not rsMyRS.EOF Then rsMyRS.MoveFirst
Do While Not rsMyRS.EOF
lstRecords.AddItem rsMyRS!Name
lstRecords.ItemData(lstRecords.NewIndex) = rsMyRS!ID
rsMyRS.MoveNext
Loop

End Sub

Private Sub lstRecords_Click()

rsMyRS.FindFirst "ID=" & Str(lstRecords.ItemData(lstRecords.ListIndex))
txtPhone.Text = rsMyRS!Phone

End Sub

 

[jebenson]

What problem are you having?

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

 

[hmckillop]

The code you have looks likes its VBA and not VB.
Are you connecting from within a module within Access or do you plan to connect from visual basic 6?


"I'm living so far beyond my income that we may almost be said to be living apart

 

[jamesoc]

im looking to connect to a database from a vb program. im only new to vb and so am having some problems

 

[jebenson]

Are you getting any error messages? If so, what are they? Also, when you get an error you can hit the Debug button on the error message dialog and you will be taken to the line of code causing the problem. Knowing this can help you (and us) find and correct the source of the error.

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

 

[MakeItSo]

Hi Jamesoc,

you need ADO-Syntax in VB anyway. You cannot simply transfer the Access-VBA DAO commands.

1.) Add "Microsoft Actve Data Objects x.x library" to your references with x.x any version like 2.5, 2.6 ...
2.) Adjust the path to the DB, this should work for you:

Private Sub lstRecord_Click()
    Dim con As ADODB.Connection
    Dim rsMyRS  As ADODB.Recordset
    Dim conStr As String
    
    conStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\Projekte\SampleDB.mdb;Persist Security Info=False"
    con.Open conStr
    rsMyRS.Open "SELECT * FROM MyTable", con
    
    Do While Not rsMyRS.EOF
        lstRecords.AddItem rsMyRS("Name")
        lstRecords.ItemData(lstRecords.NewIndex) = rsMyRS("ID")
        rsMyRS.MoveNext
    Loop
End Sub

Private Sub lstRecords_Click()

rsMyRS.FindFirst "ID=" & Str(lstRecords.ItemData(lstRecords.ListIndex))
txtPhone.Text = rsMyRS!Phone

End Sub

3.) Leave away your code in Form_LOad as it does nothing...

Hope this helps.

Regards,
Andy

[blue]An eye for an eye only ends up making the whole world blind. - "Mahatma" Mohandas K. Gandhi[/blue]
Check out this:

http://www.ccnx.net/member.html

 

[johnwm]

There is no reason why you shouldn't use DAO to access an MDB file from VB. Just ensure that you have added a reference to the DAO Object Library, then your code should work.

Project|References|Microsoft DAO 3.6 Object Library

You should Dim your objects as DAO objects for clarity:
Dim dbMyDatabase As DAO.Database
Dim rsMyRS As DAO.Recordset

Also close your recordset when you're finished with it:
rsMyRS.Close
Set rsMyRS = Nothing

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'

http://www.essexsteam.co.uk

for steam enthusiasts

 

[jamesoc]

tried to run code but getting a user defined error here Dim con As ADODB.Connection

 

[MakeItSo]

Sounds like a DAO/ADO conflict.

What references have you set now? ADO or DAO?
How have you dimmed your variables?
All as ADODB.* or DAO.* ?

Regards,
Andy


[blue]An eye for an eye only ends up making the whole world blind. - "Mahatma" Mohandas K. Gandhi[/blue]
Check out this:

http://www.ccnx.net/member.html