Tech Support Guy banner
Status
Not open for further replies.

Solved: Access 2007 VBA Error '3078': Unknown

12K views 9 replies 3 participants last post by  scotty718 
#1 ·
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:

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
And the Function RunActionQuery is here:

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
 
See less See more
#5 ·
Thanks for the compliment. I assume you mean something like setting hte SQL through a variable, then using the DoCmd.RunSQL method?

If so, I've tried that with pretty much the same SQL that is in the query and I couldn't get it to work. Actually, that was my original plan, and I played and played and played and couldn't get it work. Then a colleague told me about workspaces and that's when I went the route that I am on now.

It baffles me because it's the only one not to work.

If my assessment of what you are saying is wrong, I am more than happy to be enlightened by your idea.
 
#6 ·
Getting the VBA Syntax correct with SQL is pretty hard.
Try the form in the attached database to try and convert your Query's SQL to see if that will work.

Do the other queries that work have this on the end of the sql "CountryProfiles.accdb"?

Other options are running a macro in the other database just for that query.
Using a VBA Recordset to so what the query does.
 

Attachments

#8 ·
Thanks for your help, OBP. Unfortunately, I cannot open your file. I assume it's some sort of zip, but my PC at work won't recognize it.

That said, in my haste yesterday, I did fail to mention that I did get the VBA SQL right and ended up with the same error I am writing about - Error 3078: Unknown.

I am sooooo baffled. To reiterate: the query runs fine when I open the other database and execute it. The other query in the same database runs just fine when called through the code. As the two make table are structurally the same - they just make tables with different quieres - I even deleted the query that wasn't working and then copied the one that was and modified it to pull another query. Still, same error! I then created a make table query using another random query in the same database, and that works fine! Bear in mind, the make table queries are alll making tables in another db.

The access Gods are apparently teaching me a lesson the hard way with this project :)

@Rockn - of course, I am stepping through code, that is how I found the error.
 
#9 ·
Re-Re-Update: FINALLY FOUND ERROR SOURCE
The query that wasn't working had this line in the criteria for the date field: >DateAdd("m",-6,DMax("[Long Date (calc)]","tblArcherData")). I ran it through code without this in the criteria and it worked fine. Now it's time to figure out how to get the last 6 months worth of data in a way that the code can run the query.

Re-Update: Nevermind. Just made another query based off the linked table and then made a make-table query on that query, ran the VBA code and voila. Worked to perfection!

Update: I think I've found the reason the error occurs, the query that is failing in VBA has a linked table in the structure of the underlying query that the make table query is acting on. That's the only difference I see amongst the queries that do work and the one that doesn't.
 
#10 ·
Issue solved. Nothing wrong with VB Code, except that it wouldn't run the make table query which had an underlying query with a domain aggregate in the query. I found a dirty solution to get the appropriate date range I need, by changing the criteria in the underlying query from >DateAdd("m",-6,DMax("[Long Date (calc)]","tblArcherData")) to > Date () - 183
 
Status
Not open for further replies.
You have insufficient privileges to reply here.
Top