ADO Connection to the Current Access 97 Database 1

[scking]

I'm attempting to connect to the current Access 97 database using ADO. ADO examples on various tip boards take the easy way out and show how to connect to an Access 2000 database (See below). I've attempted to use the CurrentDb.Connection object and fail, I've also attempted to build the connection string using "Data Source:=" & CurrentDb.Name. Everything I've attempted has failed. Although I have successfully used the CurrentProject connection in Access 2000, I'm having difficulty connecting to the current project tables in Access 97.

Sub ADOGetCurrentDatabase()
Dim cnn As ADODB.Connection
Set cnn = CurrentProject.Connection
End Sub Growth follows a healthy professional curiosity

 

[Klopper]

Hello,

I see this is an old post...ever get it sorted cos I am struggling with the the same thing!

Regards
Klopper

 

[oharab]

Why are you insisting on using ADO? You can mix and match DAO in your A2k files.
Make sure a reference is set to DAO 3,6 in your references then make sure you prefix your objects with ADO. or DAO. so you get

Dim db as DAO.Database
Dim rst as DAO.OpenRecordset
.
.
.

HTH

Ben ----------------------------------
Ben O'Hara
bo104@westyorkshire.pnn.police.uk
----------------------------------

 

[BMarkham]

Try this to open a connection to an Access 97 database:

Dim conn As ADODB.Connection

conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.3.51;
Data Source=C:\MyDatabase.mdb"
conn.Open

If it's an Access 2000 database, the Provider is Microsoft.Jet.OLEDB.4.0

 

[Klopper]

Hi Ben, thanks for the post!

I am messing around with ADO for the first time!

Is it actually possible to use ADO in Access 97?
I can't get it to recognise the currentproject keyword (ie is stays lower case and Access 907 reckons it is an undeclared variable at run time. Have played with all the reference settings but to no avail...

Thanks
Klopper

 

[oharab]

Not sure if Ive mixed things up a bit.
AFAIK you cannot use ado in A97. I thought you were connecting to an old A97 db in A2k.

Sorry if I have confused things.

B ----------------------------------
Ben O'Hara
bo104@westyorkshire.pnn.police.uk
----------------------------------

 

[BMarkham]

You can use ADO from Access 97.

From any module window go to Tools menu, References... Scroll down through the list, find "Microsoft ActiveX Data Objects 2.6 Library", and put a check it. You may have several versions of ADO installed on your system (I have 2.0, 2.1, 2.6, 2.7). Just pick the highest version number. But only pick one!

 

[BMarkham]

P.S.
After you've set a refernce to ADO, you have to be very specific when declaring variables. Eg:

Dim myRS as ADODB.Recordset
Set myRS = New ADODB.Recordset

Since both ADO and DAO have recordset objects you need to include the ADODB or DAO prefix when declaring them. There are other shared objects as well.

 

[oharab]

Well you learn something every day! I didn't know ADO was part of A97, I assume that it's just not the default in the same way DAO isn't default in A2k.
Does this mean I'll be able to rewrite my DAO code in ADO using A97, then have it move straight to A2k or are there some incompatabilities I need to be aware of.

B ----------------------------------
Ben O'Hara
bo104@westyorkshire.pnn.police.uk
----------------------------------

 

[BMarkham]

Well, ADO isn't part of Access97 per se. It's a component installed on your computer that you can use in your Access 97 databases by setting a reference to it. Much the same way you could set a reference to Microsoft Excel and create spreadsheets from your Access databases (if you really wanted to...)

So the code for ADO will work the same whether you use it from Access97, Access2000, VB6.0, Microsoft Word...... whatever.

Brett Markham

 

[scking]

Hey gents, I solved my original question ages ago. ADO is an ActiveX object which is not part of either Access 97 or Access 2000 but is used to connect to OLEDB, ODBC, or SQLServer. I have used ADO to successfully extract the data from Access (either 97 or 2000) and place it in Excel. The concepts are the same if it is in Access. The below function does more than just build a recordset and copy it into a worksheet but you can analyze and modify as needed. I also built a function that would run a stored procedure on Access from Excel through ADO. But that is another story.


Public Function CreateReportFromQueryADO(Optional DbPath As String, _
Optional SQL_Query As String) As Boolean

Dim cnnConn As ADODB.Connection
Dim rstRecordset As ADODB.Recordset
Dim cmdCommand As ADODB.Command
Dim ws As Worksheet
Dim iCols As Integer

On Error Resume Next

' Open the connection.
Set cnnConn = New ADODB.Connection
'With cnnConn
' .ConnectionString = _
' "Provider=Microsoft.Jet.OLEDB.4.0"
' .Open DefaultDbPath
'End With

' If the DbPath was provided verify it
' otherwise open from the default database.
If Len(DbPath) > 0 Then
' Check to verify that the database exists at the location stated
' in the input parameter
If Len(Dir(DbPath)) > 0 Then
With cnnConn
'.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & DbPath & ";" _
& "Jet OLEDB:System Database=C:\Dmats\system2000.mdw;" _
& "Jet OLEDBatabase Password=guest"
.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0"
.Open DbPath
End With
Else
MsgBox "Invalid database path (" & DbPath & &quot"
Exit Function
End If
Else
If Len(Dir(DefaultDbPath)) > 0 Then
With cnnConn
.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0"
.Open DefaultDbPath & ";UID=guest;PWD="
End With
Else
MsgBox "Invalid database path (" & DefaultDbPath & &quot"
Exit Function
End If
End If

On Error GoTo HandleErr

' Set the command text.
Set cmdCommand = New ADODB.Command
Set cmdCommand.ActiveConnection = cnnConn
With cmdCommand
.CommandText = SQL_Query
.CommandType = adCmdText
.Execute
End With

If cnnConn.Errors.Count > 0 Then
Err.Raise 10621, "cnnConn", "Error Returns following execute."
End If

' Open the recordset.
Set rstRecordset = New ADODB.Recordset
Set rstRecordset.ActiveConnection = cnnConn
rstRecordset.Open cmdCommand

' If there are no records then just notify the user
' and exit the procedure.
If rstRecordset.EOF Then
MsgBox "There are no records for this request. Please " _
& "review your request to ensure it is correct."
Exit Function
Else
CreateNewSheet
End If

'Worksheets.Add Count:=1, Before:=Sheets(1)
Set ws = Worksheets(1)

For iCols = 0 To rstRecordset.Fields.Count - 1
ws.Cells(1, iCols + 1).Value = rstRecordset.Fields(iCols).Name
Next

ws.Range(ws.Cells(1, 1), _
ws.Cells(1, rstRecordset.Fields.Count)).Font.Bold = True
ws.Range("A2&quot.CopyFromRecordset rstRecordset

Exit_Proc:
On Error Resume Next
' Close the connections and clean up.
cnnConn.Close
Set cmdCommand = Nothing
Set rstRecordset = Nothing
cnnConn.Close
Set cnnConn = Nothing
Exit Function

HandleErr:
Select Case Err.Number
Case Else
Call HandleTheError("", "frmB2ReportGenerator.CreateReportFromQueryADO", Err)
End Select

Resume Exit_Proc
Resume
End Function ----------------------
Steve King
scking@arinc.com
Life is filled with lessons.
We are responsible for the
results of the quizzes.
-----------------------

 

[Klopper]

scking, do stored procedures in access exist?

 

[scking]

Microsoft handles a query as a stored procedure using ADO. You can therefore use ADO as in the following:

Dim cnnConn As ADODB.Connection
Set cnnConn = New ADODB.Connection

' If the DbPath was provided verify it
' otherwise open from the default database.
If Len(DbPath) > 0 Then
' Check to verify that the database exists at the location stated
' in the input parameter
If Len(Dir(DbPath)) > 0 Then
With cnnConn
'.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & DbPath & ";" _
& "Jet OLEDB:System Database=C:\Dmats\system2000.mdw;" _
& "Jet OLEDBatabase Password=guest"
.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0"
.Open DbPath
.CursorLocation = adUseServer
End With
Else
MsgBox "Invalid database path (" & DbPath & &quot"
Exit Function
End If
Else
If Len(Dir(DefaultDbPath)) > 0 Then
With cnnConn
.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0"
.Open DefaultDbPath & ";UID=guest;PWD="
End With
Else
MsgBox "Invalid database path (" & DefaultDbPath & &quot"
Exit Function
End If
End If

On Error GoTo HandleErr
' Set the command text.
Set cmdCommand = New ADODB.Command
Set cmdCommand.ActiveConnection = cnnConn

If Len(Param1) > 0 Then
Set prmParameter = cmdCommand.CreateParameter("MyParam", adVariant, _
adParamInput, Len(Param1))
cmdCommand.Parameters.Append prmParameter
prmParameter.Value = Param1
End If

With cmdCommand
.CommandText = Proc
.CommandType = adCmdStoredProc
.Execute
End With

If cnnConn.Errors.Count > 0 Then
Err.Raise 10621, "cnnConn", "Error Returns following execute."
End If

----------------------
scking@arinc.com
Life is filled with lessons.
We are responsible for the
results of the quizzes.
-----------------------