DAO::Open a Connection Problem

[navigator703]

I am still quite new to VB and databases. Well a buddy of mine is having problems with his DAO connection to an Access database. Since I have no clue about DAO, I have turned here.

I have also looked at connectionstrings.com, but I didn't find anything with DAO connections. Any suggestions?

Here is the code below:

Option Explicit


Dim db As DAO.Database
Dim dbPath As String
Dim rsDB As DAO.Recordset
Dim numRows As Integer
Dim numCols As Integer
Dim RowIndex As Integer
Dim ColIndex As Integer
Dim I As Integer
Dim counter As Integer
Dim theUsedRange As Range

Function Ex_Update()

counter = 0


'-------------------------------------
'Find the used range on the worksheet
'-------------------------------------
Set theUsedRange = ActiveSheet.UsedRange numRows = theUsedRange.Rows.Count numCols = theUsedRange.Columns.Count


'---------------------------------------------
'Open up the database and the desired table
'---------------------------------------------
dbPath = "h:\My Documents\Mike.Hron.Stuff\JADOCS-NC.mdb"   
Set db = OpenDatabase(dbPath)
'<---------This is where it crashes
Set rsDB = db.OpenRecordset("PED-Equipment", dbOpenTable)   'I've tried
everything here, relative path, full path. Nothing works

For I = 2 To numRows
    With rsDB
        .FindFirst "Item # = Cells(I, A)"
        If .NoMatch Then
        ' cmdUpdate.Enabled = True
         .AddNew
         .Fields(1).Value = Cells(I, 1).Value
         .Fields(2).Value = Cells(I, 2).Value
         .Fields(3).Value = Cells(I, 3).Value
         .Fields(4).Value = Cells(I, 4).Value
         .Fields(5).Value = Cells(I, 5).Value
         ' field 6 placeholdler NOT IN DATABASE
         .Fields(6).Value = Cells(I, 7).Value
         .Fields(7).Value = Cells(I, 8).Value
         .Fields(8).Value = Cells(I, 9).Value
         .Fields(9).Value = Cells(I, 10).Value
         .Fields(10).Value = Cells(I, 11).Value
         .Fields(11).Value = Cells(I, 12).Value
         .Fields(12).Value = Cells(I, 13).Value
         .Fields(13).Value = Cells(I, 14).Value
         .Fields(14).Value = Cells(I, 15).Value
         .Fields(15).Value = Cells(I, 16).Value
         .Fields(16).Value = Cells(I, 17).Value
         .Fields(17).Value = Cells(I, 18).Value
         .Fields(18).Value = Cells(I, 19).Value
         .Fields(19).Value = Cells(I, 20).Value
         .Fields(20).Value = Cells(I, 21).Value
         .Fields(21).Value = Cells(I, 22).Value
         .Fields(22).Value = Cells(I, 23).Value
         .Fields(23).Value = Cells(I, 24).Value
         .Fields(24).Value = Cells(I, 25).Value
         .Fields(25).Value = Cells(I, 26).Value
         .Fields(26).Value = Cells(I, 27).Value
         .Fields(27).Value = Cells(I, 28).Value
         .Fields(28).Value = Cells(I, 29).Value
         .Fields(29).Value = Cells(I, 30).Value
         .Fields(30).Value = Cells(I, 31).Value
         .Fields(31).Value = Cells(I, 32).Value
         .Fields(32).Value = Cells(I, 33).Value
         .Fields(33).Value = Cells(I, 34).Value
         .Fields(34).Value = Cells(I, 35).Value
         .Fields(35).Value = Cells(I, 36).Value
         .Fields(36).Value = Cells(I, 37).Value
         .Fields(37).Value = Cells(I, 38).Value
         .Fields(38).Value = Cells(I, 39).Value
         .Update
         counter = counter + 1
        
        Else: .MoveFirst
        End If
    End With
Next I
MsgBox (counter & " records added to database") End

End Function

 

[JerryKlmns]

Set recordset = object.OpenRecordset (source, type, options, lockedits)

In a Microsoft Jet workspace, if object refers to a QueryDef object, or a dynaset- or snapshot-type Recordset, or if source refers to an SQL statement or a TableDef that represents a linked table, you can't use dbOpenTable for the type argument; if you do, a run-time error occurs.

So, PED-Equipment has to be a local table in that database!

 

[IT4EVR]

If the above post doesn't provide the answer, the link below provides an example of opening a recordset in DAO in Visual Basic.

http://support.microsoft.com/kb/148361/en-us

What sort of error are you getting anyway? It would be a good idea to implement some sort of error-handling in this function.