-
Jan 15th, 2021, 02:22 PM
#1
Thread Starter
Addicted Member
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.
-
Jan 15th, 2021, 03:12 PM
#2
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).
-
Jan 15th, 2021, 05:43 PM
#3
Thread Starter
Addicted Member
Re: Import from Excel into Access' table with VB6
Originally Posted by SamOscarBrown
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.
-
Jan 15th, 2021, 10:44 PM
#4
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
-
Jan 15th, 2021, 11:01 PM
#5
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|