Alternative to Application.LoadFromText for MS Access Queries

Question

I am attempting to load from text files, queries into an MS Access Queries Collection using VBScript. I am using something like this: The code originated from Here.

 for each myFile in folder.Files
    objecttype = fso.GetExtensionName(myFile.Name)
    objectname = fso.GetBaseName(myFile.Name)
    WScript.Echo "  " & objectname & " (" & objecttype & ")"

    if (objecttype = "form") then
        oApplication.LoadFromText acForm, objectname, myFile.Path
    elseif (objecttype = "bas") then
        oApplication.LoadFromText acModule, objectname, myFile.Path
    elseif (objecttype = "mac") then
        oApplication.LoadFromText acMacro, objectname, myFile.Path
    elseif (objecttype = "report") then
        oApplication.LoadFromText acReport, objectname, myFile.Path
    elseif (objecttype = "sql") then
        'oApplication.LoadFromText acQuery, objectname, myFile.Path
        ' Add create querydef code here
    end if

 next

But I am unsure how to create a query definition using VBScript.

Any ideas?

Note: I originally Exported to the file using something like this:

For Each myObj In oApplication.CurrentDb.QueryDefs 
   Set f = fso.CreateTextFile(sExportpath & "\" & myObj.Name & ".sql", True) 
   f.WriteLine(myObj.SQL) 
   f.Close 
Next

score 4 · Accepted Answer · answered May 13 '09 at 19:14

4

This saves the query defs

For i = 0 To db.QueryDefs.Count - 1
    Application.SaveAsText acQuery, db.QueryDefs(i).Name, sExportpath & "\" & db.QueryDefs(i).Name & ".sql"
Next i

Then the LoadFromText should work

answered May 13 '09 at 19:14

DJ.

16,045
3
42
46

1

Although rare, when you have a query that has a subquery as an input table, LoadFromText will work, but will create a corrupt query. Inside the exported query txt file, there will be a section with "Begin InputTables" and the table "Name" value will be your subquery SQL. After the import, access interprets this name as a querydef name. The workaround is to parse the query export file, look for subqueries, and "unpack" them during the export process. I ran into this because I had to write an auto-fix routine that cleaned out corrupted binary blocks from Access Objects. – DHW Aug 08 '14 at 19:13

score 4 · Answer 2 · answered May 13 '09 at 20:19

Just for completeness I wanted to add my solution.

if (objecttype = "form") then
    oApplication.LoadFromText acForm, objectname, myFile.Path
elseif (objecttype = "bas") then
    oApplication.LoadFromText acModule, objectname, myFile.Path
elseif (objecttype = "mac") then
    oApplication.LoadFromText acMacro, objectname, myFile.Path
elseif (objecttype = "report") then
    oApplication.LoadFromText acReport, objectname, myFile.Path
elseif (objecttype = "sql") then
    oApplication.LoadFromText acQuery, objectname, myFile.Path
end if

Apparently all that needed to be added was:

  Const acQuery = 1

Thanks to DJ

Alternative to Application.LoadFromText for MS Access Queries

2 Answers2

Linked