Solved

Modify SQL in VBA

Posted on 2011-02-28
4
391 Views
Last Modified: 2012-05-11
I have a make table query that adds a table to a back end database. I need to modify the location of this table from VBA. Here is my query in design view:

SELECT tblJobNumbers.ID, tblJobNumbers.[Job Number] INTO tblJobNumbersLoc IN 'C:\Program Files\Timesheet Database\TSDB JobsLoc and SubmitLoc.mdb'
FROM tblJobNumbers
WHERE (((tblJobNumbers.ID) Like "1249"));

How can I modify this query in VBA?
0
Comment
Question by:threeieng
4 Comments
 
LVL 25

Expert Comment

by:reb73
ID: 34998636
Not 100% sure what you are alluding to, perhaps the link below puts you on the right path -

http://www.tek-tips.com/viewthread.cfm?qid=1118666&page=2
0
 
LVL 28

Expert Comment

by:omgang
ID: 34998779
Assuming the query name is MyQuery; change accordingly
OM Gang


Public Function TestQuerySQL()

On Error GoTo Err_TestQuerySQL

    Dim qdf As DAO.QueryDef
    Dim strSQL As String, strOldPath As String, strNewPath As String
    Dim intStart As Integer, intEnd As Integer
   
    strNewPath = "c:\temp\NewDB.mdb"     '<------- set path to new db location here

    Set qdf = CurrentDb.QueryDefs("MyQuery")
    strSQL = qdf.SQL  
        'get the start location of the current db path in the SQL statement
    intStart = InStr(strSQL, "IN '") + 4
        'get the end location
    intEnd = InStr(intStart, strSQL, "' From")
        'grab the full path from the current SQL statement
    strOldPath = Mid(strSQL, intStart, (intEnd - intStart))
        'replace the current path with the new path
    strSQL = Replace(strSQL, strOldPath, strNewPath)
    'Debug.Print strSQL
        'update the SQL statement in the QueryDef object
    qdf.SQL = strSQL

Exit_TestQuerySQL:
    Set qdf = Nothing
    Exit Function

Err_TestQuerySQL:
    MsgBox Err.Number & ", " & Err.Description, , "Error in Function TestQuerySQL of Module Module8"
    Resume Exit_TestQuerySQL

End Function
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 34998796
Build your SQL as a string:

strSQL = <create string similar to your present SQL.>
CurrentDb.Execute(strSQL)

/gustav
0
 
LVL 9

Accepted Solution

by:
Armen Stein - Microsoft Access MVP since 2006 earned 500 total points
ID: 34999943
First, MakeTable is not a great approach.  It adds more to database bloat, and you lose some control over the structure of the resulting table.

A better approach is to use a persistent table with exactly the structure you want.  Then you can run a query to Delete all records from it, and another one to Append the ones you want.  Much cleaner.

Also, with this approach you now have the ability to use the table as a normal linked table - no need to hardcode the file location as you show in your example.

Cheers,
Armen
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now