I have a master access table where we store all the employee information. I have various application developed in excel which imports and uses information in master.
Now I want to develop a excel application that will import all the records from access table or some particular data (defined by field id) in excel spreadsheet. (***successfull y acomplished)
User would be able to view the records and make changes to the spreadsheet now when the user closes the file excel will ask if records needs to be updated to the master, if user selects yes I need a VBA code to run through a check which will compare the master with spreadsheet and update the records.
I know I can link the excel spreadsheet and do this easily, however we use this in our office through our network and there are about 50 people who could need to update it at once. Now excel spreadsheet cannot be opened and updated by all at once.
Please let me know if there is a method through which I can use to do this.
Access Master Records have employee info. like EMPID, Name, Manager Name, Date of Joining, Designation, Email.
12 8173 NeoPa 32,586
Recognized Expert Moderator MVP
This sounds like a complicated setup. I would advise against using one such as this as there are so many complications involved, such as which update to keep when there is more than one from the many instances.
That said, the easiest and most reliable would clearly not be a simple Excel process. The Excel part could export a file for the Access part to import. It could even trigger the Access code to start by opening the database. It would be sensible to include the date/time in both the master and the Excel tables so only the latest would be used. Clearly only the updated records should be included in the exports.
I was able to achieve little bit... - Sub UpdateMDB()
-
Dim cn As Object
-
Dim rs As Object
-
strFile = Workbooks(1).FullName
-
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile _
-
& ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
-
Set cn = CreateObject("ADODB.Connection")
-
Set rs = CreateObject("ADODB.Recordset")
-
cn.Open strCon
-
strSQL = "SELECT * FROM [Sheet1$] s " _
-
& "INNER JOIN [;Database=D:\temp excel\T.mdb;].Table1 t " _
-
& "ON s.id=t.id " _
-
& "WHERE s.Field1<>t.Field1"
-
rs.Open strSQL, cn, 1, 3
-
strSQL = "UPDATE [;Database=D:\temp excel\T.mdb;].Table1 t " _
-
& "INNER JOIN [Sheet1$] s " _
-
& "ON s.id=t.id " _
-
& "SET t.Field1=s.Field1 " _
-
& "WHERE s.Field1<>t.Field1 "
-
cn.Execute strSQL
-
End Sub
Above code compares the sheet data and writes it however does not update if any new records are updated in excel.
Is there a way to have excel to update any new data found using the same batch technique. Also can I incorporate it in the below mentioned code in any way.
NeoPa 32,586
Recognized Expert Moderator MVP
I would make a few suggestions. - Always declare your procedures as Public or Private explicitly (Line #1).
- Wherever possible, declare your variables explicitly (Lines #2 & #3).
- Avoid use of numbered indices with collections (Line #4). It is harder to read, less obvious what the code is doing.
- Use indentation. This helps a reader to make sense of what your code is doing.
A revised version of your code below to illustrate these points : - Public Sub UpdateMDB()
-
Dim cn As ADODB.Connection
-
Dim rs As ADODB.Recordset
-
-
strFile = ActiveWorkbook.FullName
-
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile & _
-
";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
-
Set cn = CreateObject("ADODB.Connection")
-
Set rs = CreateObject("ADODB.Recordset")
-
cn.Open strCon
-
strSQL = "SELECT * FROM [Sheet1$] s " & _
-
"INNER JOIN [;Database=D:\temp excel\T.mdb;].Table1 t " & _
-
"ON s.id=t.id " & _
-
"WHERE s.Field1<>t.Field1"
-
rs.Open strSQL, cn, 1, 3
-
strSQL = "UPDATE [;Database=D:\temp excel\T.mdb;].Table1 t " & _
-
"INNER JOIN [Sheet1$] s " & _
-
"ON s.id=t.id " & _
-
"SET t.Field1=s.Field1 " & _
-
"WHERE s.Field1<>t.Field1 "
-
cn.Execute strSQL
-
End Sub
To handle new records added to the Excel sheet, consider changing the last SQL to start with [Sheet1$] then use a LEFT JOIN to match up with [Table1]. If ADODB supports OUTER JOINs (SQL Server does but I don't think you can with Jet) then use that instead, and you can handle deleted records too.
NeoPa 32,586
Recognized Expert Moderator MVP
Sorry.
Forget the last suggestion. I overlooked the point that it was an UPDATE query. You can only update existing records. Separate INSERT and DELETE queries would be required to do the job completely.
@NeoPa
Thanks will try that. However when in terms of going for a separate INSERT and DELETE queries I know how to do that individual, but how to compare it with existing records on excel.
NeoPa 32,586
Recognized Expert Moderator MVP
I presume you would INSERT any records that are in your workbook, but not already in [Table1]. DELETE should be for those records which are currently in [Table1] but which aren't in your workbook.
@NeoPa
Yes you are absolutely right. Do you think it can be done...
There's no chance of running a small SQL server of sorts? What about attaching it to an existing server? If you are using an MDB file, they are definitely not made to be used by multiple users at the same time, whereas SQL tables are. EDIT: I retract this statement of multiple concurrent users not being a part of MDB files. See post below
Also, if you go the route of SQL, this would save a lot of effort on your part in designing it and make it more versatile in what it can update. For instance, if you decide to move the employee records to some global corporate database (which could use Oracle, SQL server, etc.), it's just a nip and a tuck to do so with this. With your method it seems like it would take a total body reconstruction.
NeoPa 32,586
Recognized Expert Moderator MVP thelonelyghost: If you are using an MDB file, they are definitely not made to be used by multiple users at the same time, whereas SQL tables are.
While Access databases are not able to handle multiple concurrent users as well as full, server based, solutions are, they are nevertheless, designed to handle multiple concurrent users. You may certainly find that the practical limit can be exceeded well before something like SQL Server, but that is some way short of meaning multiple users are not handled. It does depend on correct configuration for them of course.
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Paul |
last post by:
I was wondering if anyone has had an issue where using vba code to
read an excel file and import the data into an access table some
records are not imported from the excel file. It seems looking at the
data in the excel file that if the first character in the excel file
cell is numeric it will read and write only numeric values only. If I
sort the coloumn in the excel file and the first character in the cell
read is alphanumeric then only...
|
by: john_liu |
last post by:
How can I update an Access table based on a sheet in Excel by VBA in excel.
Thanks
|
by: info |
last post by:
I can successfully open a recordset based upon an Excel sheet in
Access, but I can't work out how to copy all the records to an Access
table. Any pointers?
|
by: winzy |
last post by:
I have a table in Access which needs updating from an Excel file.
Instead of importing the whole Excel like a new table in Acess, how do
I do a partial import/update??
For example, Access table has 100 rows of records and 10 columns of
attributes. The Excel table has only 50 rows of records and and 2
attributes. The key IDs for the 50 rows and names for the 2 attributes
are already defined in the Access table.
Thanks in advance!!
|
by: ruthboaz |
last post by:
Hi,
I am exporting a table with data to an excel file.. Some fields are empty in the exported file while the others and the key field, has values.
The empty field(s) in excel, will be entered with values by another department.
I require to update the access db from the updated excel fields to the respective fields in the database..
While using import from the file menu, I see that the information is append the table..
| |
by: jalmar |
last post by:
Hello....I'm back again with the same question. I still haven't been able to figure out why my query isn't working. I am linking a table to an Excel spreadsheet, that part is fine, it is even updating correctly (when I update in Excel, the Access table is updated) my problem is when I query out the information that I need from this table-it creates far too many records. I asked this question before and was asked for the SQL but couldn't find...
|
by: christianlott1 |
last post by:
I've linked an excel worksheet as an access table. The values appear
but it won't allow me to change any of the values.
To test I've provided a fresh blank workbook and same problem.
I've done this on other computers and it's worked fine.
Using:
Win 2000 SP4
|
by: OfficeDummy |
last post by:
Hello all!
I searched the Internet and this forum for a similar problem, but I found no help... Also, I'm a complete newbie to the fascinating world of programming, VBA and Access, so my question can very well be very stupid.
The tasks are:
1)Import an Excel table into Access
2)Add a new column and fill it with variables of date/time type.
Steps 1 and 2 need to be done only once, and I've almost managed to accomplish them. Now...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
| |
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |