specify default directory with OLEDB

When using ODBC connectivity you can specify a DefaultDir. But with OLEDB you can't use the DefaultDir setting, is there an OLEDB equivalent?

VB Code:

' CONNECTION STRING FOR THE DATABASE
'    AccessConnect = "DRIVER={Microsoft Access Driver (*.mdb)};" & _
'                    "DBQ=Westfield.MDB;" & _
'                    "DefaultDir=" & App.Path & ";"
 
AccessConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                 "Data Source=Westfield.mdb;"

You should specify it as part of the Data Source parameter, ie:

VB Code:

AccessConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                 "Data Source=" & App.Path & "\Westfield.mdb;"

thanks let me try that :cool:

Ok, I did that but I must have another issue. I am trying to switch from ODBC to OLEDB, so in my module I am createing a connection. I need to modify my ODBC connection to the new OLEDB. But it appears that I something is wrong. I am getting errors when I call my connection. I guess I need to make some additional changes.

3709,"The connection cannot be used to perform this operation. It is either closed or invalid in this context.","This error created on:",#2006-02-07 10:07:19#

This is how I have my connection in my module set up. As you can see I was using ODBC.

VB Code:

Sub CreateConnection(objConn As Connection)
 
' CONNECTION STRING FOR THE DATABASE
'    AccessConnect = "DRIVER={Microsoft Access Driver (*.mdb)};" & _
'                    "DBQ=Westfield.MDB;" & _
'                    "DefaultDir=" & App.Path & ";"
 
AccessConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                 "Data Source==" & App.Path & "\Westfield.mdb;"
 
                     
                     'MsgBox AccessConnect
 
    ' Create Connection Object and open it
    Set objConn = New ADODB.Connection
 
    objConn.CursorLocation = adUseClient
 
    ' Trap any error/exception
    On Error GoTo AdoError
 
    objConn.ConnectionString = AccessConnect
    objConn.Open
 
    Exit Sub
 
    ' ADO Error/Exception Handler
AdoError:
    ErrNumber = Err.Number
    ErrSource = Err.Source
    ErrDescription = Err.Description
 
    'AdoErrorEx List1, objConn
 
End Sub

This is how I was calling it from within appilcation, it bombs out on this line and gives me the error above.

.ActiveConnection = objConn

VB Code:

'connect to database, generate ID, add ID to text box
    Call CreateConnection(objConn)
 
    'MsgBox objConn.State
    Set objRs = New ADODB.Recordset
 
    With objRs
        .ActiveConnection = objConn
        .CursorLocation = adUseClient
        .CursorType = adOpenDynamic
        .LockType = adLockOptimistic
        .Source = "select * from Campaign where 2=3"    '2=3 basically creates a empty record set
        .Open
    End With
 
    '  ADDING RECORDS FROM TEXTBOX TO DATABASE
    With objRs
....

The Connection is not open when it is used as an ActiveConnection.

The CreateConnection procedure should be generating an error because you have two == signs after Data Source.

Code:

---

AccessConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                "Data Source==" & App.Path & "\Westfield.mdb;"

---

Man.. what dumb oversight :o

Thanks its working now.