UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Clean Up Code, Access 2013    
 
   
g0049978
post Sep 20 2019, 03:56 PM
Post#1



Posts: 26
Joined: 13-September 19



Any suggestions on cleaning up this code since I have multiple transferspreadsheet and RunSQL commands related to 1 specific table: Open6pm

Sub importdata()

DoCmd.SetWarnings False


DoCmd.DeleteObject acTable, "Closed6am"
DoCmd.DeleteObject acTable, "Open6pm"

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Closed6am", "C:\Users\Public\Closed6am.xlsx", True
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Open6pm", "C:\Users\Public\Open6pm.xlsx", True, Range:="Enteral!H:H"

DoCmd.RunSQL "Alter table Open6pm ADD Source CHAR(25), Match INT NULL, ReportDateTime CHAR(25)"
DoCmd.RunSQL "UPDATE Open6pm SET Open6pm.Source = 'Enteral' WHERE (((Open6pm.Source) Is Null));"


DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Open6pm", "C:\Users\Public\Open6pm.xlsx", True, Range:="Auto Provider!H:H"
DoCmd.RunSQL "UPDATE Open6pm SET Open6pm.Source = 'AutoProvider' WHERE (((Open6pm.Source) Is Null));"


DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Open6pm", "C:\Users\Public\Open6pm.xlsx", True, Range:="Future!H:H"
DoCmd.RunSQL "UPDATE Open6pm SET Open6pm.Source = 'Future' WHERE (((Open6pm.Source) Is Null));"


DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Open6pm", "C:\Users\Public\Open6pm.xlsx", True, Range:="Sleep - Attached!H:H"
DoCmd.RunSQL "UPDATE Open6pm SET Open6pm.Source = 'SleepAttached' WHERE (((Open6pm.Source) Is Null));"


DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Open6pm", "C:\Users\Public\Open6pm.xlsx", True, Range:="CGM!H:H"
DoCmd.RunSQL "UPDATE Open6pm SET Open6pm.Source = 'CGM_Diabetic' WHERE (((Open6pm.Source) Is Null));"


DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Open6pm", "C:\Users\Public\Open6pm.xlsx", True, Range:="Diabetic Supplies!H:H"
DoCmd.RunSQL "UPDATE Open6pm SET Open6pm.Source = 'CGM_Diabetic' WHERE (((Open6pm.Source) Is Null));"


DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Open6pm", "C:\Users\Public\Open6pm.xlsx", True, Range:="Breast Pumps!H:H"
DoCmd.RunSQL "UPDATE Open6pm SET Open6pm.Source = 'BreastPumps' WHERE (((Open6pm.Source) Is Null));"


DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Open6pm", "C:\Users\Public\Open6pm.xlsx", True, Range:="FL Blue Medicare!H:H"
DoCmd.RunSQL "UPDATE Open6pm SET Open6pm.Source = 'FLBlueMedicare' WHERE (((Open6pm.Source) Is Null));"


DoCmd.RunSQL "DELETE Open6pm.[Intake ID], Open6pm.* FROM Open6pm WHERE (((Open6pm.[Intake ID]) Is Null));"
DoCmd.RunSQL "UPDATE Open6pm SET Open6pm.ReportDateTime = Format(Now(),'mm/dd/yyyy') & ' ' & TimeValue('08:00am');"


Call updatecalc

End Sub
Go to the top of the page
 
Doug Steele
post Sep 20 2019, 04:29 PM
Post#2


UtterAccess VIP
Posts: 22,210
Joined: 8-January 07
From: St. Catharines, ON (Canada)


Is there anyway you can simply link to the Excel workbook and use SQL to update the table?

BTW, having tables with names like Closed6am and Open6pm suggests that your database may not be properly normalized. Those names imply something about the data, something that might be better contained in the table itself.

--------------------
Doug Steele, Microsoft Access MVP (2000-2018)
Personal webpage
Microsoft profile
Co-author: Access Solutions: Tips, Tricks, and Secrets from Microsoft Access MVPs, published by Wiley
Co-author: Effective SQL: 61 Specific Ways to Write Better SQL, published by Addison-Wesley Professional
Technical Editor: Access 2010 Bible, Access 2013 Bible, Access 2016 Bible, all published by Wiley
Technical Editor: SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL, 4th Edition, published by Addison-Wesley Professional
Go to the top of the page
 
g0049978
post Sep 20 2019, 04:40 PM
Post#3



Posts: 26
Joined: 13-September 19



I have code that moves Excel files from SharePoint to a local drive and from our sharedrive to the local drive. My code deletes these tables and then replaces them on a daily basis. That's why I don't link them because deleting the tables via code may cause the link to break. The reason I bring them into a local directory is that one of the files is spit out by an Oracle database and called an Excel file via .xlsx; however, Access won't recognize the file as a true Excel file until it's opened and resaved as .xlsx for some weird reason.
Go to the top of the page
 
g0049978
post Sep 20 2019, 04:42 PM
Post#4



Posts: 26
Joined: 13-September 19



I agree, we need a lot of work on normalizing our data. I need to get to the source and build out from there but at this time, just trying to automate what I got...
Go to the top of the page
 
WildBird
post Sep 20 2019, 07:25 PM
Post#5


UtterAccess VIP
Posts: 3,620
Joined: 19-August 03
From: Auckland, Little Australia


Apart from normalising etc, is best to use variables for the table names

CODE
Dim strTableName as string

strTableName = "Open6pm"

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, strTableName, "C:\Users\Public\Open6pm.xlsx", True, Range:="Enteral!H:H"


or
CODE
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, strTableName, "C:\Users\Public\" & strTableName & ".xlsx", True, Range:="Enteral!H:H"


You could also do a table with the source and build a recordset and loop that, instead of having a line for each.

Could factorise it a bit, I just cant help at the moment, got too much work on sorry, but give you ideas and maybe you can build from there. People here can help with specifics.

Cheers

--------------------
Beer, natures brain defragging tool.
Go to the top of the page
 
g0049978
post Sep 23 2019, 02:42 PM
Post#6



Posts: 26
Joined: 13-September 19



I wouldn't even know how to loop this because after it appends each sheet to the table, it has to update the associated column with the sheet name.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    22nd October 2019 - 06:50 PM