Solved

Excel cell value to access database

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

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

691 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