Results 1 to 6 of 6

Thread: Check the condition before upload Excel to Access

  1. #1

    Thread Starter
    Member
    Join Date
    Sep 2012
    Posts
    51

    Check the condition before upload Excel to Access

    Access have tables:
    StudentID Name Address Telephone

    My Excel are:
    Column A Column B Column C Column
    StudentID Name Address Telephone

    I want to write a condition in order to check field in My Excel file if one of Column in Excel is missing and the field in Access update one of field is missing = 0.
    For Example:
    My Excel are:
    Coluum A Coluum B Coluum C
    Name Address Telephone
    John Alaska 8848421
    As a result, my Access have tables in belows:
    StudentID Name Address Telephone
    0 John Alaska 8848421

    I am appreciate for your help.

  2. #2
    PowerPoster
    Join Date
    Jul 2006
    Location
    Maldon, Essex. UK
    Posts
    6,334

    Re: Check the condition before upload Excel to Access

    What do you want to do if there's more than 1 student ID mssing? (e.g. start at zero and then keep adding 1 for each missing ID, just put zero in all missing IDs)

    (If the ID is a Key field in your Access Table then each one must be unique.)

    EDIT: Here's one method you may like to try:
    Code:
    Option Explicit
    
    Private Const AUTO_NUMBER As Boolean = True     'Change this to False to set missing StudentIDs to zero
    Private Const AUTO_PREFIX As String = "AUTO"    'When Auto Numbering this will be the prefix (e.g. AUTO1)
    Private Const AUTO_START As Long = 1            'When Auto Numbering this will be the starting value
    
    Private db As ADODB.Connection
    '
    ' Assumes:  An open ADODB Connection named 'db'
    '           The Table to be updated is named 'Students'
    '           All columns in the Table are Character Types
    '           Column 'StudentID' in the Table is NOT a key field if AUTO_NUMBER is 'False'
    '           A Project Reference to the appropriate Excel Object Library
    '           A Command Button is drawn on the Form, named 'cmdExport'
    '           The Excel WorkBook containing the data is in 'C:\MyDir\MyWorkBook.xls'
    '           The Data to be exported is in the first Sheet of the WotkBook
    '
    Private Sub cmdExport_Click()
    Dim oExcel As Object
    Dim xlWbook As Excel.Workbook
    Dim xlRange As Excel.Range
    Dim xlWSheet As Excel.Worksheet
    Dim lngRowCount As Long
    Dim lngAuto As Long
    Dim lngI As Long
    Dim intI As Integer
    Dim strData(3) As String
    Dim strTable As String
    Dim strFile As String
    Dim strSQL As String
    strTable = "Students"
    strFile = "C:\MyDir\MyWorkBook.xls"
    lngAuto = AUTO_START
    '
    ' Empty the Students Table
    '
    strSQL = "DELETE FROM " & strTable
    db.Execute strSQL
    '
    ' Establish an Excel session, open the WorkBook
    ' and set the WorkSheet
    '
    Set oExcel = CreateObject("Excel.Application")
    Set xlWbook = oExcel.Workbooks.Open(strFile)
    Set xlWSheet = xlWbook.Worksheets(1)
    '
    ' Find out how many rows are in use and set up the loop
    '
    lngRowCount = xlWSheet.UsedRange.Rows.Count
    For lngI = 1 To lngRowCount
        '
        ' Check if the first column is empty
        ' if it is then set a value depending on whether AUTO_NUMBER is set
        '
        Set xlRange = xlWSheet.Range("A" & CStr(lngI))
        If Not IsEmpty(xlRange.Value) Then
            strData(0) = xlRange.Value
        Else
            If AUTO_NUMBER Then
                strData(0) = AUTO_PREFIX & CStr(lngAuto)
                lngAuto = lngAuto + 1
            Else
                strData(0) = "0"
            End If
        End If
        '
        ' Copy the data from the other columns into the Array
        '
        For intI = 1 To 3
            Set xlRange = xlWSheet.Range(Chr(Asc("A") + intI) & CStr(lngI))
            strData(intI) = xlRange.Value
        Next intI
        '
        ' Set up the SQL to INSERT the data into the Students Table
        ' and execute it
        '
        strSQL = "INSERT INTO "
        strSQL = strSQL & strTable
        strSQL = strSQL & " (StudentID, StudentName, StudentAddress, StudentNumber) Values ("
        strSQL = strSQL & "'" & strData(0) & "',"
        strSQL = strSQL & "'" & strData(1) & "',"
        strSQL = strSQL & "'" & strData(2) & "',"
        strSQL = strSQL & "'" & strData(3) & "'"
        strSQL = strSQL & ")"
        db.Execute strSQL
    Next lngI
    '
    ' Tidy up and exit
    '
    xlWbook.Close (False)
    Set xlRange = Nothing
    Set xlWSheet = Nothing
    Set xlWbook = Nothing
    Set oExcel = Nothing
    db.Close
    Set db = Nothing
    MsgBox "Finished: " & CStr(lngRowCount) & " Rows Exported to Table: " & strTable
    Unload Me
    End Sub
    Last edited by Doogle; May 26th, 2013 at 03:41 AM.

  3. #3

    Thread Starter
    Member
    Join Date
    Sep 2012
    Posts
    51

    Re: Check the condition before upload Excel to Access

    Thanks Doogle. I try it out your example but the program is error "db.Execute". I wonder if you do not declare the Access directory and may be the program is not running????

  4. #4
    I'm about to be a PowerPoster! Hack's Avatar
    Join Date
    Aug 2001
    Location
    Searching for mendhak
    Posts
    58,333

    Re: Check the condition before upload Excel to Access

    Do you have a reference set to ADO?

    Also, saying you are getting an error is not much help. There are lots of errors that could occur. What, specifically, is the error that you are getting? What does the error message say?

  5. #5
    Discovering Life Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    12,001

    Re: Check the condition before upload Excel to Access

    Why do you need VBA/vb6/or any code for this? You could manually check if the column exists or not and then simply import the excel file in Access Database from MS Access.
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved


    MyGear:
    ★ CPU ★ Ryzen 5 5800X
    ★ GPU ★ NVIDIA GeForce RTX 3080 TI Founder Edition
    ★ RAM ★ G. Skill Trident Z RGB 32GB 3600MHz
    ★ MB ★ ASUS TUF GAMING X570 (WI-FI) ATX Gaming
    ★ Storage ★ SSD SB-ROCKET-1TB + SEAGATE 2TB Barracuda IHD
    ★ Cooling ★ NOCTUA NH-D15 CHROMAX BLACK 140mm + 10 of Noctua NF-F12 PWM
    ★ PSU ★ ANTEC HCG-1000-EXTREME 1000 Watt 80 Plus Gold Fully Modular PSU
    ★ Case ★ LIAN LI PC-O11 DYNAMIC XL ROG (BLACK) (G99.O11DXL-X)
    ★ Monitor ★ LG Ultragear 27" 240Hz Gaming Monitor
    ★ Keyboard ★ TVS Electronics Gold Keyboard
    ★ Mouse ★ Logitech G502 Hero

  6. #6
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    9,143

    Re: Check the condition before upload Excel to Access

    @koolsid.....you are correct, of course...importing Excel worksheets/books to MS Access is an cinch using Access. One of the reasons "I" do similar to what the OP is asking, is that I give my VB6 programs to others. I keep their DB tables (or some of them anyway) uptodate by emailing them an excel spreadsheet which they import with one of the VB6 routines I created (unually on a menu option). That way, users don't have to know Excel, nor Access, just click and go. This is PROBABLY what OP is trying to do on a learning scale....whatcha think?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width