Solved

Excel cell value to access database

Posted on 2013-06-04
3
254 Views
Last Modified: 2013-06-07
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
Comment
Question by:pg1533
  • 2
3 Comments
 

Author Comment

by:pg1533
ID: 39220285
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
 
LVL 35

Accepted Solution

by:
[ fanpages ] earned 500 total points
ID: 39221230
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
 

Author Closing Comment

by:pg1533
ID: 39230134
Thank you
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question