Acces

[Fredn]

Can i open a database connection to an Acces 2000 database using a connectionstring?
(like i open a SQLserver database?)

The following code doesn't work; can you make it work?

'references: Microsoft ActiveX Data Objects 2.6 Library
 
Dim Conn As ADODB.Connection
Dim RS As ADODB.Recordset
Dim CS As String
 
CS = "DRIVER={Microsoft Access Driver (*.mdb)};" & "DBQ=C:\ProjectsVB\Experiment\testdb.mdb;DefaultDir=;UID=;PWD=;"
 
Conn.Open CS

[FMCAR10]

You declare the connection and the recordset but you never instantiate them. Try adding:

Set Conn = New Connection

Before opening connection.

Note: you will also have add this:

Set RS = New RecordSet

[Fredn]

That's great help ! It works now, but now i have a problem with Conn.Open CS

Is my connectionstring build right?

[Fredn]

I'm sorry, the problem is not that. My connection opens, but it is with something else i had problems:

Set Conn = New ADODB.Connection
Set RS = New ADODB.Recordset
CS = "DRIVER={Microsoft Access Driver (*.mdb)};" & "DBQ=C:\ProjectsVB\Experiment\testdb.mdb;DefaultDir=;UID=;PWD=;"
Conn.Open CS
SQL = "Select * from tb_Customers"
Set RS = Conn.Execute(SQL)
RS.MoveFirst
RS.MoveLast
MsgBox RS.RecordCount
Set Conn = Nothing
Set RS = Nothing

The error occurs on RS.Movelast. This seems to be a problem with this recordset.
When i don't do movelast and movefirst, i get RecordCount = -1, although there are several records in my table.

[FMCAR10]

By default you are opening the recordset with server side cursor.
Try adding this:

Conn.CursorLocation = adUseClient

[Fredn]

This doesn't work. I also tried changing the .CursorType to each of the 4 types, but none of them works. I always get a message "Rowset does not support fetching backwards"

[FMCAR10]

Not sure what to tell you. This works for me:
----------------------------------------------------------
Option Explicit
Private adoRs As New ADODB.Recordset
Private Conn As New ADODB.Connection

Private Sub Form_Load()

Conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=G:\Genetics\Genetics.mdb;Persist Security Info=False"
Conn.CursorLocation = adUseClient
Conn.Open

Set adoRs = Conn.Execute("Select * from change")

adoRs.MoveFirst
adoRs.MoveLast

MsgBox adoRs.RecordCount

End Sub

Server side cursors will always return -1 for a record count.

[Fredn]

This works for me too, thank you very much.

Your connectionstring was the one i was looking for. The one i used came from some site i found, but i already guessed there was something odd.

Now i can continue with my program...
thanks again

--> Why is it always the easy stuff i don't know ...

[Fredn]

I want to show the results of my query in a grid.

Can i say something like:
Grid.recordsource = RS ???

What grid do i have to take then? (i see 3 kinds of them: datagrid control, data bound grid control, ms flex grid control)

[FMCAR10]

Set datagrid1.datasource = rs

The datagrid control works fine.

[Fredn]

What is the difference between datagrid and data bound grid? And flex grid, what is that used for?

I first tried this thing:
DGrid.DataSource = RS
But it didn't work.

In your example i saw that i had to do:
SET dgrid.datasource = RS
(this works)

When do i have to use Set and when not?