Solved

Excel cell value to access database

Posted on 2013-06-04
3
218 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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

760 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now