Using ADO get access database name

[sridhar]

Hi guys,



I am using ADO to connect to Access (.mdb) database. I want to populate data from access to excel, edit the data in excel and post the modified data back to access.

My first task is to get the name of the database. I am using ADO for that.

Please find the code below which gives me an error saying "Object or provider is not capable of performing requested action". I am just trying to open a connection and use OpenSchema to get the name of the database.

Dim cn As New ADODB.Connection
Dim rsTemp as New ADODB.Recordset

cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & FileToOpen & ";"
cn.CursorLocation = adUseServer
cn.Open

Set rsTemp = cn.OpenSchema(adSchemaCatalogs)

Debug.Print "*********** START ***********" & vbCr
While Not rsTemp.EOF
Debug.Print "Catalog name: " & rsTemp!CATALOG_NAME & vbCr
rsTemp.MoveNext
Wend
Debug.Print "*********** END ***********" & vbCr

rsTemp.Close
Set rsTemp = Nothing
cn.Close
Set cn = Nothing

I tried various types like adSchemaSchemata and many more. They give me the same error. I did lot of googling but nothing helped me.

Any help is truly appreciated..

Thank you

[llkhoutx]

"FileToOpen" should include the complete path, file name and file extension.