Run Make Table Query in a closed database

[villy]

Hello Excel Gurus,

I have run a query from excel macro but only one thing is that I created a make table query and I need to run it without opening the database. I know it is possible I also found some codes regarding this issue but nothing's really answer my needs.

What I want is to run make table query through a closed database.
Thanks in advance for all your help.

Regards,

Villy

 

[dk]

Can you post your SQL?

EDIT: Actually, can you just post the entire macro?

 

[villy]

This one is working with an open database
how can I make this works in a closed database.
Thanks

Function forRepStep_1()
path = "P:\03_Construction\4.0 CONSTRUCTION AUTOMATION\4.1 Applications\QMD - PORT\Port_QMD_prog_1.9.12.mdb"
Set dbs = OpenDatabase(path) 'workspaces(0).OpenDatabase(Path)
DoCmd.SetWarnings False
 
DoCmd.RunSQL ("SELECT IRF.ID, IRF.IRFNumP, IRF.IRFNumB, IRF.IRFNumW, IRF.IRFNumN, IRF.IRFNumBound, IRF.Rev, IRF.BuildingID, IRF.DateDoc, IRF.DateTimeAct, IRF.Memo, IRF.Desc, IRF.RejectReasonID, IRF.StatusDesc, IRF.StatusAction, IRF.CP, IRF.MStatusID, IRF.DspCode, IRF.StatusID1, IRF.StatusID2, IRF.StatusID3, IRF.ByWhomID, IRF.NCRNum, IRF.IsClosed, IRF.GivenID, IRF.ClosedDateTime, propervalue([MStatusID]) & propervalue([StatusID1]) & propervalue([StatusID2]) & propervalue([StatusID3]) AS Status4, GetPrjWeek([DateTimeAct]) AS PrjW, forRepITP.Object, forRepITP.ITPNumShort, IRF_Person.Name AS OBIName, Fac.FacNameS, IRF.ClosingComment, IRF.TrackingComment0, IRF.TrackingComment1, Sched_Discipline.DiscipDisp, IRF.ClosedDateTimeSys, IRF.ItemDesc, IRF.DateTimePlan, IRF_Person_1.Name AS NDIAName, IRF.ClosedPrjWeek, IRF.ITPAct, IRF.ContractorNumber INTO forRepStep1 " & _
        "FROM ((((IRF LEFT JOIN forRepITP ON IRF.ITPNum = forRepITP.ITPNumFull) LEFT JOIN IRF_Person ON IRF.ByWhomID = IRF_Person.Alias) LEFT JOIN Fac ON (IRF.IRFNumB = Fac.FacID) AND (IRF.CP = Fac.CPID)) LEFT JOIN Sched_Discipline ON (IRF.DspCode = Sched_Discipline.DspCode) AND (IRF.CP = Sched_Discipline.CP)) LEFT JOIN IRF_Person AS IRF_Person_1 ON IRF.ByWhomIDNDIA = IRF_Person_1.Alias " & _
        "WHERE (((IRF.CP)<>"""") AND ((IRF.MStatusID)<>0) AND ((IRF.DspCode)<>""""));")
 
DoCmd.SetWarnings True
 
End Function

Halt at DoCmd.RunSQL
RunTime Error:'2075'
This operation requires an open database.

 

[villy]

Sub ConnectExample()
    Dim oCN As Object
    
    Set oCN = CreateObject("ADODB.Connection")
    oCN.Provider = "Microsoft.JET.OLEDB.4.0"
    oCN.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
             "Data Source=P:\03_Construction\4.0 CONSTRUCTION AUTOMATION\4.1 Applications\QMD - PORT\Port_QMD_prog_1.9.12.mdb;"

    oCN.Execute "SELECT 1 AS Dummy, forRepStep1.ID, forRepStep1.IRFNumP, forRepStep1.IRFNumB, IIf([MstatusID]<4,[IRFNumB] & "" - "" & [IRFNumW] & "" - "" & [IRFNumN] & "" R"" & [rev],"""") AS IRFNumSimple, forRepStep1.IRFNumBound, forRepStep1.Rev, forRepStep1.BuildingID, forRepStep1.DateDoc, forRepStep1.DateTimeAct, forRepStep1.Memo, forRepStep1.Desc, forRepStep1.RejectReasonID, forRepStep1.StatusDesc, forRepStep1.StatusAction, forRepStep1.CP, forRepStep1.DspCode, forRepStep1.MStatusID, Sched_Status0.MonitoringType, forRepStep1.StatusID1, forRepStep1.StatusID2, forRepStep1.StatusID3, forRepStep1.ByWhomID, forRepStep1.NCRNum, forRepStep1.IsClosed, forRepStep1.GivenID, forRepStep1.ClosedDateTime, forRepStep1.Status4, forRepStep1.PrjW, forRepStep1.Object, forRepStep1.ITPNumShort, forRepStep1.OBIName, forRepStep1.FacNameS, forRepStep1.ClosingComment, forRepStep1.TrackingComment0, forRepStep1.TrackingComment1, Sched_Status.Msg, Sched_Status.IsError, forRepStep1.DiscipDisp," & _
                "IIf(IsNull([Rcode]),""Unknown"",[Rcode] & "": "" & [Reason]) AS RjR, forRepStep1.IRFNumW, forRepStep1.IRFNumN AS Expr1, forRepStep1.ClosedDateTimeSys, Sched_Status.NOCReq, Sched_Status.Cap3, forRepStep1.ItemDesc, forRepStep1.DateTimePlan, forRepStep1.NDIAName, IIf(propertext([ByWhomID])="""",""Contractor"",""KPIZ"") AS Own, forRepStep1.ClosedPrjWeek, forRepStep1.ITPAct, forRepStep1.ContractorNumber INTO forRepStep2 " & _
                "FROM ((forRepStep1 LEFT JOIN Sched_Status ON forRepStep1.Status4 = Sched_Status.Status4) LEFT JOIN Sched_Status0 ON forRepStep1.MStatusID = Sched_Status0.Status1) LEFT JOIN RejectReason ON forRepStep1.RejectReasonID = RejectReason.RCode;"
    oCN.Close
    Set oCN = Nothing
End Sub

RunTime Error:
Undefined function 'propertext' in expression.

 

[dk]

Is Propertext a function defined within your Access database? Can you post the code for that too?

 

[villy]

Public Function ProperText(St) As String
    On Error GoTo Er1
    If IsNull(St) Then
        ProperText = ""
    Else
        ProperText = St
    End If
    Exit Function
Er1:
    ProperText = ""
End Function

 

[dk]

Hopefully this should work. I'm heading home now but will check back later so let me know how you get on

    oCN.Execute "SELECT 1 AS Dummy, forRepStep1.ID, forRepStep1.IRFNumP, forRepStep1.IRFNumB, IIf([MstatusID]<4,[IRFNumB] & "" - "" & [IRFNumW] & "" - "" & [IRFNumN] & "" R"" & [rev],"""") AS IRFNumSimple, forRepStep1.IRFNumBound, forRepStep1.Rev, forRepStep1.BuildingID, forRepStep1.DateDoc, forRepStep1.DateTimeAct, forRepStep1.Memo, forRepStep1.Desc, forRepStep1.RejectReasonID, forRepStep1.StatusDesc, forRepStep1.StatusAction, forRepStep1.CP, forRepStep1.DspCode, forRepStep1.MStatusID, Sched_Status0.MonitoringType, forRepStep1.StatusID1, forRepStep1.StatusID2, forRepStep1.StatusID3, forRepStep1.ByWhomID, forRepStep1.NCRNum, forRepStep1.IsClosed, forRepStep1.GivenID, forRepStep1.ClosedDateTime, forRepStep1.Status4, forRepStep1.PrjW, forRepStep1.Object, forRepStep1.ITPNumShort, forRepStep1.OBIName, forRepStep1.FacNameS, forRepStep1.ClosingComment, forRepStep1.TrackingComment0, forRepStep1.TrackingComment1, Sched_Status.Msg, Sched_Status.IsError, forRepStep1.DiscipDisp," & _
                "   IIf(IsNull([Rcode]),""Unknown"",[Rcode] & "": "" & [Reason]) AS RjR, forRepStep1.IRFNumW, forRepStep1.IRFNumN AS Expr1, forRepStep1.ClosedDateTimeSys, Sched_Status.NOCReq, Sched_Status.Cap3, forRepStep1.ItemDesc, forRepStep1.DateTimePlan, forRepStep1.NDIAName, " & _
                " IIf(IIF(ISNULL(ByWhomID),'',ByWhomID))="""",""Contractor"",""KPIZ"")""  AS Own, forRepStep1.ClosedPrjWeek, forRepStep1.ITPAct, forRepStep1.ContractorNumber INTO forRepStep2 " & _
                " FROM ((forRepStep1 LEFT JOIN Sched_Status ON forRepStep1.Status4 = Sched_Status.Status4) LEFT JOIN Sched_Status0 ON forRepStep1.MStatusID = Sched_Status0.Status1) LEFT JOIN RejectReason ON forRepStep1.RejectReasonID = RejectReason.RCode;"

 

[villy]

IIf(IIF(ISNULL(ByWhomID),'',ByWhomID))="""",""Contractor"",""KPIZ""  AS Own

Wrong number of argument

 

[dk]

Try this.

IIf(IIf(IsNull([ByWhomID]),'',[ByWhomID])='','Contractor','KPIZ') AS Own

DK

 

[villy]

Thanks dk,
I made it works yesterday I just play around the codes.
Thanks anyway

Cheers!