Hi

I have put this together but i dont think it is optimal.
The Macro is calling another Sub (UploadParameterDates) (i have done this seperatly as i may need to call just this macro)
The code then creates an access object to fire a 7 stage access macro (Various update queries etc) then closes down access

Please could you see if it could be refined? would excell handle the queries im running within access?

Which metod would boost performance of the code? using Excel to call the macro from access or Excel doing the queries?

Thanks



Code for UploadParameter Dates
Sub UploadParameterDates()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Sheets("Control").Visible = True
Sheets("Control").Select
Dim CN As Object
Dim StrDatabase As String
Dim StrWorkbook As String
Dim StrTableName As String

StrTableName = "dates"
StrDatabase = "I:\Outage Planning\YearAhead\DataBase\YAP Reporting Database.accdb"
StrWorkbook = ThisWorkbook.FullName

Set CN = CreateObject("ADODB.Connection")
With CN
.Provider = "Microsoft.ACE.OLEDB.12.0"
.ConnectionString = "Data Source=" & StrDatabase
.Open
.Execute "Delete * From Dates"
.Execute "INSERT INTO Dates(C1) SELECT * FROM [Excel 12.0;HDR=YES;Database=" & StrWorkbook & "].[Control$ag1:Ag400]"
.Close
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Application.DisplayAlerts = True

End With

End Sub
Code to be optimised/Edited


Sub Uploader()
Dim iRet As Integer
    Dim strPrompt As String
    Dim strTitle As String
    strPrompt = "By continuing, any new values will be added to the main database." & vbCrLf & _
"Are you sure you want to upload?"
    strTitle = "CRITICAL! Main Database Update Pending!"
    iRet = MsgBox(strPrompt, vbSystemModal + vbCritical + vbYesNo + vbDefaultButton2, strTitle)
    If iRet = vbNo Then
        MsgBox "Cancelled!"
    Else
    Dim iRet2 As Integer
    Dim strPrompt2 As String
    Dim strTitle2 As String
    strPrompt2 = "Are you sure you wish to continue?"
    strTitle2 = "CRITICAL!, Main Database Update Pending"
    iRet2 = MsgBox(strPrompt2, vbSystemModal + vbCritical + vbYesNo + vbDefaultButton2, strTitle2)
    If iRet2 = vbNo Then
        MsgBox "Cancelled!"
    Else
  Application.ScreenUpdating = False
  Sheets("Control").Visible = True
  Call UploadParameterDates
'do i need both objects?
Dim A As Object
Dim CN As Object
Dim StrDatabase As String
Dim StrWorkbook As String
Dim StrTableName As String
StrDatabase = "i:\outage planning\YearAhead\Database\YAP Reporting Database.accdb"
StrWorkbook = ThisWorkbook.FullName
Set CN = CreateObject("ADODB.Connection")
With CN
.Provider = "Microsoft.ACE.OLEDB.12.0"
.ConnectionString = "Data Source=" & StrDatabase
.Open
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set A = CreateObject("Access.Application")
A.Visible = False
A.OpenCurrentDatabase ("i:\outage planning\YearAhead\Database\YAP Reporting Database.accdb")
A.DoCmd.RunMacro "uploadMacro"
Application.DisplayAlerts = True
Application.ScreenUpdating = False
End With
Application.ScreenUpdating = True
End If
End If
  Sheets("Control").Select
MsgBox "Uploaded", vbInformation = vbOKOnly, "Upload Completed"
End Sub