Check Database from Excel to Access 2010

[June7]

1. The data is imported from where - spreadsheet, CSV, Oracle, SQL? Your options are to link to the source data and use them like tables to build UPDATE and INSERT action or VBA code that opens the source as an object and manipulates the object.

2. Yes it does - at least it does for me.

[Mohanss82]

Sorry June, I m lost. Below is what I require:

I am using a spreadsheet to Import the records which has the [T Vault].Id (Primary Key) and [T Vault].[Select] (Record Selectors while display on Form and displays True/False when exported). If I update certain Fields/Columns for specific records with Id (Primary Key) mentioned and Record selectors are set to True, I want the Records to be modified on the Table on a click f a button. Please help.

SQL View of the Query:
UPDATE [T Vault] SET [T Vault].Id = [Vault], [T Vault].[Select] = [Vault], [T Vault].[Client Code] = [Vault], [T Vault].[Sr No] = [Vault], [T Vault].[Contract Number] = [Vault], [T Vault].[Drawer Name] = [Vault], [T Vault].[Cheque No] = [Vault], [T Vault].[Cheque Date] = [Vault], [T Vault].[Cycle Date] = [Vault], [T Vault].[Contract Amount] = [Vault], [T Vault].[Cheque Amt] = [Vault], [T Vault].[MICR Code] = [Vault], [T Vault].[Bank Name] = [Vault], [T Vault].Branch = [Vault], [T Vault].[Payable Location] = [Vault], [T Vault].[Pick up Location] = [Vault], [T Vault].[Sent Date] = [Vault], [T Vault].[Account Number] = [Vault], [T Vault].[Type of Account] = [Vault], [T Vault].[Payment Mode] = [Vault], [T Vault].[Reviewed By] = [Vault], [T Vault].[Contract Details] = [Vault], [T Vault].[Cheque Information] = [Vault], [T Vault].[F And I MANAGER] = [Vault], [T Vault].TDS = [Vault], [T Vault].CTS = [Vault], [T Vault].[No Of Contracts Tagged] = [Vault], [T Vault].[Receipt Amount] = [Vault], [T Vault].[Cheque Status] = [Vault], [T Vault].[Imported By] = [Vault], [T Vault].[Imported Date] = [Vault]
WHERE ((([T Vault].[Select])="TRUE"));

[June7]

Query is nonsensical. Every field is set to the same parameter - [Vault].

If data was just imported, why does every field need to be modified?

After data is imported, exactly what field(s) need to be updated and with what data?

[Mohanss82]

For example, Out of all fields, One Field/Column as Bank Name alone requires to be changed for 100 rows, I would prefer export that to excel and have the changes made in excel and then import them back to DB, where Bank Name alone will be modified, if the record selector is set to be "True", else I will have to manually change those 100 rows. pls let me knw whether this will work and if so what should I do ?

[June7]

UPDATE [T Vault] SET [Bank Name] = 'some value', Select = False WHERE Select=True

[Mohanss82]

Hi,

I have the process flows as images and planning to insert them in forms. I tried inserting them as image but image is getting skewed and no option to zoom in under form view. Please suggest.

Attachment 18949

[June7]

Sorry, I have no idea what you are talking about. I have completely lost track of this thread and your last post does not seem to have any relevance to the previous posts. Don't think I will be able to help further.

[Mohanss82]

Hi June,

I have managed to build the DB per my earlier posts. Now I require the Process Flows (Images) to display at the click of a button. Please suggest.

[June7]

The only way I have ever displayed images on form is with an Image control. If you want the images to be dynamic, set the ControlSource property. The ControlSource can be an Attachment field that holds the images or can be a path to external image file. Review https://www.accessforums.net/access/...rds-48766.html

[Mohanss82]

Hi All,

I am here with new questions:

1. I have been using a System XL Report as Linked Table for Dashboard till date. Now I tried converting it as Local Table using a VBA to Refresh this table by using VBA to Clear the table and use the Transfer Spreadsheet to upload again. I see the table gets updated but the Queries & Relationships based on that table gets cleared every time. Is this due to Table being refreshed ?

2. I am cleaning up the Check database with Bank records to see the actual ones they hold as of now, but the unique key of our records is with different data type in their records. please advise.

3. I wanted to update the database with Excel file for correcting bulk entries. Suggest me an update query in SQl or VBA ?

4. How to retrieve a particular field from other table which is an calculated/ Inserted field here. Is this is possible through an function or creating an Relationship ?

[June7]

1. Why local table and not the link? How do you 'clear' table? If you are deleting and recreating the table with each import, yes that could mess up relationships. Delete records, not table.

2. Why different data type? Which is what type? Can you change yours?

3. Don't know your database. What is it you are having difficulty with? Show your attempted code/query.

4. What is the 'other' table? If it is external do you have link? Does it have PK/FK relationship with local table? If you want to import then must import to a normal field.

[Mohanss82]

1. Why local table and not the link? How do you 'clear' table? If you are deleting and recreating the table with each import, yes that could mess up relationships. Delete records, not table.

Below is my Code for Refresh:

Private Sub BtnUpdCMSData_Click()
DoCmd.DeleteObject acTable, "tbl_CMS Data"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tbl_CMS Data", "C:\Users\msivasa\Desktop\UAT\CMS Files\DFSI_CMS_Data.xlsx", True
End Sub

2. Why different data type? Which is what type? Can you change yours?

My Data type is Text, while Bank use them as Number and the problem is I cant set that as Primary key in Bank Table as there some records where Bank has left blank, hence PK cannot be set.

3. Don't know your database. What is it you are having difficulty with? Show your attempted code/query.

My SQL:

UPDATE tbl_Vault
SET tbl_Vault.Select = y.Select
FROM tbl_Vault INNER JOIN
(
SELECT [Id], [Select]
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;IMEX=1;Database=C:\Users\msivasa\Desktop\UAT\u pd.xls;', 'SELECT Id,Select FROM [SQL_Search2$]')) y
ON tbl_Vault.Id = y.Id

4. What is the 'other' table? If it is external do you have link? Does it have PK/FK relationship with local table? If you want to import then must import to a normal field.

The Scenario is My System Report is the master Table, and Vault is the Check Database and I have created a relationship for the contract Numbers whereas the Cheque Number & Cheque Amount are the additional 2 fields which I want to retrieve based on the contract Numbers.

[June7]

1. As suggested, delete records not table, then import records to existing table.
CurrentDb.Execute "DELETE FROM [tbl_CMS Data]"

2. You have field named SELECT - a reserved word? Can you change your field to number type?

3. What is OPENROWSET? Never seen that.

4. Not sure what you are telling me. Maybe you just need to build query that joins tables?

[Mohanss82]

3. I have tries an Update Query which results in an error as



Query:

UPDATE [Update Vault] SET [Update Vault].Id = [tbl_Vault], [Update Vault].ChkBox = [tbl_Vault], [Update Vault].[Client Code] = [tbl_Vault], [Update Vault].[Sr No] = [tbl_Vault], [Update Vault].[Contract Number] = [tbl_Vault], [Update Vault].[Drawer Name] = [tbl_Vault], [Update Vault].[Cheque No] = [tbl_Vault], [Update Vault].[Cheque Date] = [tbl_Vault], [Update Vault].[Cycle Date] = [tbl_Vault], [Update Vault].[Contract Amount] = [tbl_Vault], [Update Vault].[Cheque Amt] = [tbl_Vault], [Update Vault].[MICR Code] = [tbl_Vault], [Update Vault].[Bank Name] = [tbl_Vault], [Update Vault].Branch = [tbl_Vault], [Update Vault].[Payable Location] = [tbl_Vault], [Update Vault].[Pick up Location] = [tbl_Vault], [Update Vault].[Sent Date] = [tbl_Vault], [Update Vault].[Account Number] = [tbl_Vault], [Update Vault].[Type of Account] = [tbl_Vault], [Update Vault].[Payment Mode] = [tbl_Vault], [Update Vault].[Reviewed By] = [tbl_Vault], [Update Vault].[Contract Details] = [tbl_Vault], [Update Vault].[Cheque Information] = [tbl_Vault], [Update Vault].[F And I MANAGER] = [tbl_Vault], [Update Vault].TDS = [tbl_Vault], [Update Vault].CTS = [tbl_Vault], [Update Vault].[No Of Contracts Tagged] = [tbl_Vault], [Update Vault].[Receipt Amount] = [tbl_Vault], [Update Vault].[Cheque Status] = [tbl_Vault], [Update Vault].[Imported By] = [tbl_Vault], [Update Vault].[Imported Date] = [tbl_Vault]
WHERE ((([Update Vault].Id) Is Not Null) AND (([Update Vault].ChkBox)="TRUE"));

4. I am explaining the Scenario again for your valuable suggestion. System Report is used as Master Table while the Vault is the Cheque Details Table, where all the relationships are built based on Master Table and use the Check details like Cheque No & Cheque Amount while comparing with Bank Records. I created an Union Query to get the Distinct records from both ends and tried Using Dlookup & Relationship to get the info but it didn't work. Please suggest me a Query or a way to retrieve this Cheque details in query while comparing both records. Hope this helps.

Query:

SELECT [qry_Union_Weekly Contract List].[Contract Number], [tbl_DFSI Weekly Cheques].[CMS Status], [tbl_DFSI Weekly Cheques].[PDC Configuration Status], [tbl_DFSI Weekly Cheques].[Cheque Number] AS [DFSI Chq No], [tbl_Citi Weekly Banking MIS].ChequeNo AS [Citi Chq No], IIf(Right([DFSI Chq No],5)=Right([Citi Chq No],5),"Matches","Need to Check") AS [Check 1], [tbl_DFSI Weekly Cheques].[Cheque Amount] AS [DFSI Chq Amount], [tbl_Citi Weekly Banking MIS].ChequeAmount AS [Citi Chq Amount], IIf([DFSI Chq Amount]=[Citi Chq Amount],"Matches","Need to Check") AS [Check 2]
FROM ([qry_Union_Weekly Contract List] LEFT JOIN [tbl_Citi Weekly Banking MIS] ON [qry_Union_Weekly Contract List].[Contract Number] = [tbl_Citi Weekly Banking MIS].ContractNo) LEFT JOIN [tbl_DFSI Weekly Cheques] ON [qry_Union_Weekly Contract List].[Contract Number] = [tbl_DFSI Weekly Cheques].[Contract Number];

[June7]

3. The UPDATE query does not make sense. How can you update a field to entire table as shown with: [Update Vault].Id = [tbl_Vault].

UPDATE [Update Vault] INNER JOIN [tbl_Vault] ON [Update Vault].Id = [tbl_Vault].Id
SET [Update Vault].ChkBox = [tbl_Vault].ChkBox
WHERE (([Update Vault].ChkBox)="TRUE"));

This assumes the two Id fields are not autonumber type. The Id fields associate the two sets of records. It should NOT be updated, this data must be common to both tables.

4. Too hard to evaluate without having tables and other queries (such as the UNION) to review. But offhand, can't see anything wrong. Although 3 tables/queries are involved which was not my original impression.