?
Solved

Excel cell value to access database

Posted on 2013-06-04
3
Medium Priority
?
286 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 2000 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

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

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

This article describes a serious pitfall that can happen when deleting shapes using VBA.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
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…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

762 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