Results 1 to 5 of 5

Thread: Excel to Access DB

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Feb 2018
    Location
    Texas
    Posts
    130

    Excel to Access DB

    How would one go about Overwriting a particular record, or filling in just empty fields in a particular record based on a match criteria..Lets same a unique UserID Reason for wanting to do this.. It is so much easier to update a customer table in excel than in a FlexGrid or DataGrid. Thus one would want to extract the data from a database Customers Table into an Excel sheet. Edit copy and paste etc, and then push the Excel data back to the database updating each record as needed.

    I guess one could use excel directly to connect to the database but my understanding is that it is a one way push form the database to excel and not from excel to the database. Correct me if I am wrong on this.

  2. #2
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    7,699

    Re: Import from Excel into Access' table with VB6

    2-WHAT????????????????
    It is so much easier to update a customer table in excel than in a FlexGrid or DataGrid.
    Gues you don't know much about Flex and Data grids.
    Last edited by Shaggy Hiker; Jan 15th, 2021 at 10:59 PM.
    Sam I am (as well as Confused at times).

  3. #3

    Thread Starter
    Addicted Member
    Join Date
    Feb 2018
    Location
    Texas
    Posts
    130

    Re: Import from Excel into Access' table with VB6

    Quote Originally Posted by SamOscarBrown View Post
    2-WHAT???????????????? Gues you don't know much about Flex and Data grids.
    2- I agree, my knowledge IS limited in the aspect of what I asked, however, I DO know that FlexGrid and DataGrids are less flexible for user input in certain cases - especially for neophyte secretarial data input users of computers that need a familiar interface such as the Excel "Grid" - and especially it is needed for people I am working with that need a presentation layer that are not scared of. If FlexGrids and Data grids were so much easier in all cases than Excel, then I guess data entry sheets using Excel as the user's input interface for import into the applications database would not exist in many industries - heck maybe there is really no need for Excel at all.

    That said, I too, have had to work with apps that use a FlexGrid and Datagrid, and those are clunky for the data input part of the production work, (but not for data management and higher level reporting functions - kind of hard to query an Excel file, and perform certain operation on the data right?) But, of course it could be done easily, if that file were imported into the database. Indeed, that is likely what is happening with the company that the OP is likely working for. They, too, likely found that the Excel is the way to go for certain data capture entry-point functions.


    Code:
    Public Function ExcelApp() As Object
        ' This provides late binding of Microsoft Excel so that
        ' the version doesn't need to be known before binding.
        '
        ' Be SURE to execute a obj.Quit to remove the copy of Excel from memory.
        Dim obj As Object
        Dim Bad As Boolean
        '
        On Error Resume Next
            '
            Err.Clear
            Set obj = CreateObject("Excel.Application.11")
            '
            If Err <> 0 Then
                obj.Quit
                Set obj = Nothing
                Err.Clear
                Set obj = CreateObject("Excel.Application.10")
            End If
            '
            If Err <> 0 Then
                obj.Quit
                Set obj = Nothing
                Err.Clear
                Set obj = CreateObject("Excel.Application")
            End If
            '
            Select Case True
            Case Err <> 0
                Bad = True
            Case val(obj.Version) < 10
                Bad = True
            End Select
            '
            If Bad Then
                obj.Quit
                Set obj = Nothing
                MsgBox "Error.  Microsoft Excel was not found on this computer.  Microsoft Excel XP (aka 2002 or v10) or later must be installed on this computer for this program to execute this feature.  This program will now be terminated.", vbCritical, "Error."
                End
            End If
            '
            Set ExcelApp = obj
        On Error GoTo 0
    End Function
    Last edited by Shaggy Hiker; Jan 15th, 2021 at 10:59 PM.

  4. #4
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,241

    Re: Import from Excel into Access' table with VB6

    while you and some others may disagree, the policy of this forum would encourage to make a new thread, with links to any previous thread that is relevant, this is up to the moderators to decide

    as far as using excel for data input, i would believe a custom designed form with validation for all entries would be a better gui, then save the form data to access or other database, only when all entries match whatever required criteria
    limited choice fields can use list or comboboxes, name and oither custom data can use text boxes
    this is only my opinion, but plenty of people struggle with data entry in excel

    it is usually easier to use early binding (add a reference to excel) when developing excel automation as you then have intellisense for all dimensioned excel type objects, then change to late binding before deploying, remove reference and change all object variables to object type
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  5. #5
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    35,787

    Re: Excel to Access DB

    The best way to get a good answer in here is to get the most eyeballs on the question that you can. While this question is related to some past thread, it is a thread that is several years old. Many people will note that and not bother looking to see that a new question has been asked. By starting a new thread, even though it is related to some existing topic, you get fresh eyes, which makes it more likely that there will be a good answer.
    My usual boring signature: Nothing

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