• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 294
  • Last Modified:

Excel cell value to access database

Hi,

I have attached two files. They are

1. New Forecasting Tool.xls
2. ForecastToolDatabase.mdb

I am trying to update the cell value 'K8' of 'Epidemiology' worksheet onto the table named 'ftd_epidemology_alias' in the column 'prevalence_rate'. However, it is giving an error. Kindly help me on this. i have written this code on activate event of 'Epidemiology' worksheet.

Regards,
Prashanth
New-Forecasting-Tool.xls
ForecastToolDatabase.mdb
0
pg1533
Asked:
pg1533
  • 2
1 Solution
 
pg1533Author Commented:
Also, it would be of great help if this activity is applied to the button which is there on the spreadsheet. Please advise.

Thank you for your time and support.
0
 
[ fanpages ]IT Services ConsultantCommented:
Hi,

Within the modSheets.SaveHomeSheetData() function you presently have this code:
Function SaveHomeSheetData()
On Error GoTo ErrorHandler

Application.ScreenUpdating = False

Call clsADodb.ConnecttoDatabase
Dim objRset As Recordset
Set objRset = New Recordset                                         ' *** Added

    objRset.CursorType = adOpenStatic
    objRset.Open "SELECT * from ftd_epidemology_alias"
        
    clsADodb.objCon.Execute "insert into ftd_epidemology_alias(prevalence_rate) values('" & Sheet8.Range("K8").Text & "')"
    
    If clsADodb.objCon.State = adStateOpen Then clsADodb.objCon.Close
        If objRset.State = adStateOpen Then objRset.Close

        Exit Function
        
ErrorHandler:
              MsgBox Err.Description, vbInformation, "Error Information"

End Function

Open in new window


Firstly, why is this defined as a function if it does not return anything to the calling process?

To answer your question, however, I would change this routine to read:

Sub SaveHomeSheetData()

  On Error GoTo Err_SaveHomeSheetData

  Call clsADodb.ConnecttoDatabase
        
  If clsADodb.objCon.State = adStateOpen Then
     clsADodb.objCon.Execute "insert into ftd_epidemology_alias(prevalence_rate) values('" & Sheet8.Range("K8").Text & "')"
  End If
  
Exit_SaveHomeSheetData:

  On Error Resume Next
  
  If clsADodb.objCon.State = adStateOpen Then
     clsADodb.objCon.Close
  End If
  
  Exit Sub
        
Err_SaveHomeSheetData:

   MsgBox Err.Description, vbInformation, "Error Information"
   
   Resume Exit_SaveHomeSheetData

End Sub

Open in new window



PS. You also have a compilation error within the clsADODBconnect class module.  Please note the "' *** Removed" lines below:

Public Sub ClearRecordSetConnection()
    
    'If objRset.State = adStateOpen Then    ' *** Removed
    '    objRset.Close                      ' *** Removed
    '    Set objRset = Nothing              ' *** Removed
    'End If                                 ' *** Removed
    If objCon.State = adStateOpen Then
        objCon.Close
        Set objCon = Nothing
    End If
    
End Sub

Open in new window


BFN,

fp.
0
 
pg1533Author Commented:
Thank you
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now