Hello,
I am recieving a run-time error '3078': Uknown for some code I've written. I've pasted the code below, but basically, what is happening is that the code is not recognizing a query I have in another database when I know for sure it is there. I have deleted and re-created the query several times, and also renamed the query. Furthermore, there is another query in the same database that I run from this code and it works just fine. Also, the other queries I run from different databases (all in the loop) all work fine as well. It's just this one query, with the name qryMetricTable.
In case it helps, the SQL for the query is as follows:
SELECT [qryCountryProfile-Metrics].* INTO tblMetrics IN '\\nasrut157v1AML\data_grp01\Corporate Security\Anti Money Laundering\Risk Management\Country Profiles\CountryProfiles.accdb'
FROM [qryCountryProfile-Metrics];
Here is the code I have:
And the Function RunActionQuery is here:
I am recieving a run-time error '3078': Uknown for some code I've written. I've pasted the code below, but basically, what is happening is that the code is not recognizing a query I have in another database when I know for sure it is there. I have deleted and re-created the query several times, and also renamed the query. Furthermore, there is another query in the same database that I run from this code and it works just fine. Also, the other queries I run from different databases (all in the loop) all work fine as well. It's just this one query, with the name qryMetricTable.
In case it helps, the SQL for the query is as follows:
SELECT [qryCountryProfile-Metrics].* INTO tblMetrics IN '\\nasrut157v1AML\data_grp01\Corporate Security\Anti Money Laundering\Risk Management\Country Profiles\CountryProfiles.accdb'
FROM [qryCountryProfile-Metrics];
Here is the code I have:
Code:
Option Compare Database
Option Explicit
Dim strSQL As String, strDBF As String, strQ As String, strT As String ' applies to other parts of code not shown
Dim db As Database, db2 As Database
Dim rs As Recordset
Dim wrk As Workspace, wrk2 As Workspace
Dim qdf As QueryDef ' applies to other parts of code not shown
Dim lTbl As Long ' applies to other parts of code not shown
Dim i As Integer ' applies to other parts of code not shown
Public Const sqlRUN = "SELECT [tblPath].basepath, [tblPath].folder,[tblPath].dbname, [tblQuery].qname" & _
"FROM tblPath INNER JOIN tblQuery ON [tblPath].ID = [tblQuery].PathID;"
Public Const strPath = "[URL="file://nasrut157v1aml/data_grp01/Corporate"]\\nasrut157v1AML\data_grp01\Corporate[/URL] Security\Anti Money Laundering\Risk Management\Country Profiles\"
Public Function RunProfile()
Set wrk = DBEngine.Workspaces(0)
Set db = wrk.OpenDatabase(CurrentDb.Name, False, False)
Set rs = db.OpenRecordset(sqlRUN)
' makes the appropriate tables
While Not rs.EOF
strDBF = rs![basepath].value & rs![folder].value & rs![dbname].value
strQ = rs![qname].value
Call RunActionQuery(strDBF, strQ)
Application.RefreshDatabaseWindow' just so I can see the code is working
rs.MoveNext
Wend
strDBF = ""
strQ = ""
Set rs = Nothing
Set db = Nothing
End Function
Code:
Public Function RunActionQuery(ByVal strDBF As String, ByVal strQ As String) As Boolean
Set wrk2 = DBEngine.Workspaces(0)
Set db2 = wrk2.OpenDatabase(strDBF, False, False)
DoCmd.SetWarnings False
wrk2.BeginTrans
db2.Execute strQ
wrk2.CommitTrans
db2.Close
DoCmd.SetWarnings True
Set db2 = Nothing
Set wrk2 = Nothing
End Function