specify default directory with OLEDB

[Navarone]

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

' 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;"

[si_the_geek]

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

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

[Navarone]

thanks let me try that

[Navarone]

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.

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

'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
....

[brucevde]

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.

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

[Navarone]

Man.. what dumb oversight

Thanks its working now.