none
MS Access connect to SQL server using variable table name RRS feed

  • Question

  • I have a situation where a new table is created in a SQL database every month with a different name, e.g. TBL_YYYYMM (don't get me started).  I'm trying to connect to this new table programatically in an Access database.  My plan was to have the user enter the file name on a Form and then have the VB script call that field in creating the link.  Unfortunately my VBA skills aren't quite up to the task!  Here is what I've tried so far....it's not happy but it's not really throwing an error.  I will freely admit that I'm using code I copied/pasted from another website.  Is what I'm trying even possible?

    Sub SQLLink2()
    Const cstrOldName As String = "dbo_MF_MF_201712"
    Dim db As DAO.Database
    Dim tdfOld As DAO.TableDef
    Dim tdfNew As DAO.TableDef
    Set db = CurrentDb
    Set tdfOld = db.TableDefs(cstrOldName)
    tdfOld.Name = cstrOldName & "FRM!frmMF!MF" ' trying to reference the form to get the new table name

    Set tdfNew = db.CreateTableDef
    With tdfNew
        .Name = cstrOldName
        .Connect = tdfOld.Connect
        .SourceTableName = "dbo.Dual"
    End With
    db.TableDefs.Append tdfNew
    End Sub

    Tuesday, April 17, 2018 4:35 PM

All replies

  • Hi Bebesmom,

    Try to refer example below may help you.

    Public Function AttachTable(vLocalTable As String, vRemoteTable As String, vServer As String, vDatabase As String, Optional vUserName As String, Optional vPassword As String)
    On Error GoTo ErrX
    Dim td As TableDef
    Dim cnX As String    
        For Each td In CurrentDb.TableDefs
            If td.Name = vLocalTable Then
                CurrentDb.TableDefs.Delete vLocalTable
            End If
        Next      
        If Len(vUserName) = 0 Then
            '//Use trusted authentication if vUserName is not supplied.
            cnX = "ODBC;DRIVER=SQL Server;SERVER=" & vServer & ";DATABASE=" & vDatabase & ";Trusted_Connection=Yes"
        Else
            '//WARNING: This will save the UserName and the Password with the linked table information.
            cnX = "ODBC;DRIVER=SQL Server;SERVER=" & vServer & ";DATABASE=" & vDatabase & ";UID=" & vUserName & ";PWD=" & vPassword
        End If
        Set td = CurrentDb.CreateTableDef(vLocalTable, dbAttachSavePWD, vRemoteTable, cnX)
        CurrentDb.TableDefs.Append td
        AttachTable = True
        Exit Function
    ErrX:    
        AttachTable = False
        MsgBox Error$ & " (" & Err.Number & ")",, "Function> AttachTable"
    End Function

    Reference:

    Link a SQL Server table in MS Access 2010 using VBA

    Disclaimer: This response contains a reference to a third party World Wide Web site. Microsoft is providing this information as a convenience to you. Microsoft does not control these sites and has not tested any software or information found on these sites; therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, April 18, 2018 5:50 AM
    Moderator
  • Hi Bebesmom,

    Is your issue is solved now?

    I find that you did not done any follow up after creating this thread.

    If your issue is fixed by you then I suggest you to post your solution and mark it as an answer.

    If your issue is still exist then try to refer the suggestions given by the community members.

    If you think that the suggestions given by the community member can solve your issue then mark the helpful suggestion as an answer.

    It will help us to close this thread and it also can be helpful to other community members who will meet with same kind of issues in future.

    If you have any further questions then you can let us know about it, We will try to provide you further suggestions to solve it.

    I suggest you to update the status of this thread and take appropriate actions to close it.

    Thanks for your understanding.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, May 2, 2018 9:49 AM
    Moderator