In MS Access SQL Server Linked Table pass through query

Question

I have a query against a linked table in MS Access that uses the getdate() function of SQL Server. However, I get this error when I attempt to run the query:

Undefined function GetDate in function

How do I create a linked table that allows the use of SQL Server T-SQL syntax? I see that this is called a pass through query but I don't know how to set it up to use the connection on the linked table as a pass through query.

Currently using Access 2010. The query is:

select getdate()

If it helps, I used the following vba code that generates the table link to SQL Server:

Function LinkTable(LinkedTableAlias As String, Server As String, Database As String, SourceTableName As String, OverwriteIfExists As Boolean, Username As String, Password As String)
    'This method will also update the link if the underlying table definition has been modified.
    If (InStr(1, LinkedTableAlias, "MSys") > 0) Then
        Log "Skipping " & LinkedTableAlias
        Exit Function
    End If
    'The overwrite parameter will cause it to re-map/refresh the link for LinktedTable Alias, but only if it was already a linked table.
    ' it will not overwrite an existing query or local table with the name specified in LinkedTableAlias.

    'Links to a SQL Server table without the need to set up a DSN in the ODBC Console.
    Dim tdfLinked As DAO.TableDef

    ' Open a database to which a linked table can be appended.
    Dim dbsCurrent As Database
    Set dbsCurrent = CurrentDb()

    'Check for and deal with the scenario ofthe table alias already existing
    If TableNameInUse(LinkedTableAlias) Then
        'If InStr(dbsCurrent.TableDefs(LinkedTableAlias).Connect, "AccessBackup") Then
        '    Exit Function
        'End If

        If (Not OverwriteIfExists) Then
            Log "Can't use name '" + LinkedTableAlias + "' because it would overwrite existing table."
            Exit Function
        End If
        'delete existing table, but only if it is a linked table
        'If IsLinkedTable(LinkedTableAlias) Then
            dbsCurrent.TableDefs.Delete LinkedTableAlias
            dbsCurrent.TableDefs.Refresh
        'Else
        '    Log "Can't use name '" + LinkedTableAlias + "' because it would overwrite an existing query or local table."
        '    Exit Function
        'End If
    End If

    'Create a linked table
    Set tdfLinked = dbsCurrent.CreateTableDef(LinkedTableAlias)
    tdfLinked.SourceTableName = SourceTableName

    tdfLinked.Connect = "ODBC;DRIVER={SQL Server};SERVER=" & Server & ";DATABASE=" & Database & ";UID=" & Username & ";PWD=" & Password & ";"

    On Error Resume Next
    dbsCurrent.TableDefs.Append tdfLinked
    If (err.Number = 3626) Then 'too many indexes on source table for Access
            err.Clear
            On Error GoTo 0

            If LinkTable(LinkedTableAlias, Server, Database, "vw" & SourceTableName, OverwriteIfExists, Username, Password) Then
                Log "Can't link directly to table '" + SourceTableName + "' because it contains too many indexes for Access to handle. Linked to view '" & "vw" & SourceTableName & "' instead."
                LinkTable = True
            Else
                Log "Can't link table '" + SourceTableName + "' because it contains too many indexes for Access to handle. Create a view named '" & "vw" & SourceTableName & "' that selects all rows/columns from '" & SourceTableName & "' and try again to circumvent this."
                LinkTable = False
            End If
            Exit Function
    End If
    On Error GoTo 0

    '** Turn on error handling
  On Error GoTo ErrorHandler:
    tdfLinked.RefreshLink


    LinkTable = True

    Exit Function
ErrorHandler:
    Log "refreshlink failed for " & tdfLinked.Name
    LinkTable = True

Nick.Mc · Accepted Answer · 2014-09-01 02:13:13Z

1

I don't quite understand this statement:

How to I create a linked table that allows the use of SQL Server T-SQL syntax?

But this is how you convert an existing MS Access querydef to a pass through query:

Go to design mode in the query, press the Query menu command, then SQL Specific then Pass Through

See this for screenshots.

http://www.mssqltips.com/sqlservertip/1482/microsoft-access-pass-through-queries-to-sql-server/

answered Sep 1, 2014 at 2:13

Nick.Mc

19.4k6 gold badges67 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Rahul Tripathi · Accepted Answer · 2014-08-31 19:51:33Z

1

The reason why you are getting the error is that GETDATE() is not a function inside MSAccess. You probably need Now() to get the date and time or you may use Date() which provides the date

answered Aug 31, 2014 at 19:51

Rahul Tripathi

173k33 gold badges292 silver badges341 bronze badges

4 Comments

SumGuy Over a year ago

getdate() is a function in the data-source, which is ms-sql server. Does access automatically convert to t-sql when you have a linked table?

Rahul Tripathi Over a year ago

@SumGuy:- getdate() is a function in MSSQL not MSAccess and that is the reason why you are getting the error.

SumGuy Over a year ago

I understand that, but that's not my question, how do I get it to use the linked data source and do a pass through query on the source database? I updated the original question, thanks for your help so far

ludwigmace Over a year ago

Lemme splain. A linked table in Access allows you to perform Access-style SQL operations of UPDATE, SELECT, INSERT, DELETE because the table-linking (via ODBC, whatever) handles all the translation work for you. A pass-through query simply passes SQL directly to the source database and returns the results to Access. Note that you cannot treat it like a table for anything other than a SELECT statement. That said, if you're still interested in how to do a pass-through query I'll post an example.

ludwigmace · Accepted Answer · 2014-09-01 03:08:14Z

0

Here's a quick and dirty VBA way to create a pass-through query:

Set qdf = CurrentDb.CreateQueryDef("testqry")
' this is just your connection string
qdf.Connect = "ODBC;Driver={SQL Server};Server=MSSQL1; Database=MyDB;Trusted_Connection=Yes"
'anything here gets passed directly to and executed on the SQL Server  
qdf.SQL = "select getdate()"
Set qdf = Nothing

Now you can use "testqry" as if it's any other Access query (as far as SELECTing from it goes, anyway)

answered Sep 1, 2014 at 3:08

ludwigmace

7124 silver badges10 bronze badges

Comments

Albert D. Kallal · Accepted Answer · 2014-09-01 20:41:43Z

0

Simple save your t-sql query as a pass-though

Select GetDate()

Then in VBA code, you can go:

TheSqlDate =  currentdb.QueryDefs("qPass").OpenRecordset()(0)

Using ADO, and hardcoding connection strings, and the HUGE whacks of other code posted here is just a way to rack up billable hours and create world poveity. My posted solution IS ONLY ONE LINE OF CODE!

answered Sep 1, 2014 at 20:41

Albert D. Kallal

50.1k3 gold badges41 silver badges64 bronze badges

Collectives™ on Stack Overflow

In MS Access SQL Server Linked Table pass through query

4 Answers 4

Comments

4 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related