Problem with Acces database connection

saviles

New member
Joined
Mar 13, 2016
Messages
1
Reaction score
0
Points
0
Hi friends, I have a macro which helps me to import data of any .mdb file. It works on 2003 excel plataforma but not in 2010.

I have bolded error sentence, please if anybody can help me with this. Thanks!


Option Explicit


Sub GetHDM4Files()
Dim TheRunData As String
TheRunData = Worksheets("Main Menu").Range("RunDataDirectory").Value
If Not Right(TheRunData, 1) = "\" Then
TheRunData = TheRunData & "\RunData.mdb"
Else
TheRunData = TheRunData & "RunData.mdb"
End If
If Len(Dir$(TheRunData)) = 0 Then
MsgBox TheRunData & " is not a valid Run Data Directory.", 16
Exit Sub
End If
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Application.Calculation = xlManual
GetHDM4RunData (TheRunData)
If Range("NoSections").Value * Range("NoSensitivity").Value > 64000 Then
MsgBox "Number of Sections X Number of Sensitivity Scenarios > 64,000", 16
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Application.Calculation = xlAutomatic
Exit Sub
End If
GetSections (TheRunData)
GetOptions (TheRunData)
GetSensitivityScenarios (TheRunData)
GetAnnualdata (TheRunData)
SetupResults
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Application.Calculation = xlAutomatic
Sheets("Main Menu").Select
MsgBox "Done collecting HDM-4 results."


End Sub



Private Sub GetHDM4RunData(TheRunData)
Dim MyRecordSet As DAo.Recordset
Dim MyDatabase As DAo.Database
Dim NoRecords As Integer, i As Integer, NoBudgets As Integer, TheCheck As Integer
Set MyDatabase = OpenDatabase(TheRunData)
Set MyRecordSet = MyDatabase.OpenRecordset("HDM4RunData", dbOpenDynaset)
Worksheets("HDM4RunData").Range("A2:k2").ClearContents
MyRecordSet.MoveLast
NoRecords = MyRecordSet.RecordCount
MyRecordSet.MoveFirst
i = 1
Do While Not MyRecordSet.EOF
Worksheets("HDM4RunData").Cells(i + 1, 1).Value = MyRecordSet.Fields("DESC")
Worksheets("HDM4RunData").Cells(i + 1, 2).Value = MyRecordSet.Fields("STUDY_TYPE")
Worksheets("HDM4RunData").Cells(i + 1, 3).Value = MyRecordSet.Fields("RUNDATE")
Worksheets("HDM4RunData").Cells(i + 1, 4).Value = MyRecordSet.Fields("START_YEAR")
Worksheets("HDM4RunData").Cells(i + 1, 5).Value = MyRecordSet.Fields("NUM_YEARS")
Worksheets("HDM4RunData").Cells(i + 1, 6).Value = MyRecordSet.Fields("NUM_VEH")
Worksheets("HDM4RunData").Cells(i + 1, 7).Value = MyRecordSet.Fields("NUM_SEC")
Worksheets("HDM4RunData").Cells(i + 1, 8).Value = MyRecordSet.Fields("ANALMODE")
Worksheets("HDM4RunData").Cells(i + 1, 9).Value = MyRecordSet.Fields("CURRENCY")
Worksheets("HDM4RunData").Cells(i + 1, 10).Value = MyRecordSet.Fields("DISC_RATE")
Worksheets("HDM4RunData").Cells(i + 1, 11).Value = MyRecordSet.Fields("NUMSENSCEN")
MyRecordSet.MoveNext
i = i + 1
Loop
MyRecordSet.Close
MyDatabase.Close
End Sub
 
Back
Top