MS Access AttachDSNLessTable

in MS Access 2010 I am using “AttachDSNLessTable” to link table in SQL Server to my Access database.

Call AttachDSNLessTable("My_Access_tblName", "SQL_Table_Name", "Server", "Database", "", "") this is working ok.

Now I created view in my SQL Server database named  “SQL _View_Name”. I am trying to connect to that view as I did with table :
Call AttachDSNLessTable("My_Access_tblName", "SQL_View_Name", "Server", "Database", "".””)
 but getting error.
: AttachDSNLessTable encounter an unexpected error:
The Microsoft Access database engine could not find the object ‘SQL_View_Name”.
Make sure the object exists and that you spell its name and the path name correctly.
 If ‘SQL_View_Name’ is not a local object,check you network connection or contact Aministrator.

This is function AttachDSNLessTable.

'//Name     :   AttachDSNLessTable
'//Purpose  :   Create a linked table to SQL Server without using a DSN
'//Parameters
'//     stLocalTableName: Name of the table that you are creating in the current database
'//     stRemoteTableName: Name of the table that you are linking to on the SQL Server database
'//     stServer: Name of the SQL Server that you are linking to
'//     stDatabase: Name of the SQL Server database that you are linking to
'//     stUsername: Name of the SQL Server user who can connect to SQL Server, leave blank to use a Trusted Connection
'//     stPassword: SQL Server user password
Function AttachDSNLessTable(stLocalTableName As String, stRemoteTableName As String, stServer As String, stDatabase As String, Optional stUsername As String, Optional stPassword As String)
    On Error GoTo AttachDSNLessTable_Err
    Dim td As TableDef
    Dim stConnect As String
   
    For Each td In CurrentDb.TableDefs
        If td.Name = stLocalTableName Then
            CurrentDb.TableDefs.Delete stLocalTableName
        End If
    Next
     
    If Len(stUsername) = 0 Then
        '//Use trusted authentication if stUsername is not supplied.
        stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";Trusted_Connection=Yes"
    Else
        '//WARNING: This will save the username and the password with the linked table information.
        stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";UID=" & stUsername & ";PWD=" & stPassword
    End If
    Set td = CurrentDb.CreateTableDef(stLocalTableName, dbAttachSavePWD, stRemoteTableName, stConnect)
    CurrentDb.TableDefs.Append td
    AttachDSNLessTable = True
    Exit Function

AttachDSNLessTable_Err:
   
    AttachDSNLessTable = False
    MsgBox "AttachDSNLessTable encountered an unexpected error: " & Err.Description

End Function
TarasAsked:
Who is Participating?
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor Infotrakker SoftwareCommented:
Also try setting a Breakpoint in the code and determining the exact state of your variables when you're trying to create the new View link. You might find that you've misspelled something, or that you have properly prefixed the schema, as ste5an suggested.
0
 
Kelvin SparksConnect With a Mentor Commented:
Check that you have assigned to view the appropriate permissions (SELECT, INSERT, UPDATE and DELETE) for the user that you're connecting as in SQL Server.


Kelvin
0
 
ste5anConnect With a Mentor Senior DeveloperCommented:
Also check whether you're using the correct schema prefix.
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
TarasAuthor Commented:
schema prefix is the same,  permission  are the same on objects.
0
 
Kelvin SparksCommented:
I always include the schema prefix in the SQL Object name being passed to the dsn linking code. I use the same type of code exclusively for connecting to SQL Server (including to views), so I think that there's something in what you're passing.

Kelvin
0
 
bfuchsConnect With a Mentor Commented:
@Kelvin,

I also had once a similar error message (no table found) that appeared only for views and worked fine for tables, remember that?-:)

https://www.experts-exchange.com/questions/28093155/ADP-docmd-transfertext-produces-'table-not-found'-error-when-using-it-for-a-view.html
0
 
Kelvin SparksCommented:
@bfuchs

Yes, in your case that was an Access Project. This code is for an Access database (.mdb or .accdb). The code here would be meaningless in an Access Project.

Kelvin
0
 
bfuchsCommented:
The code here would be meaningless in an Access Project
Actually I meant the opposite, the way you managed to solve that issue perhaps can be applied here as well,
as the symptoms are the same..
0
 
Kelvin SparksCommented:
@bfuchs

Yes, symptoms are the same, but unfortunately the solutions aren't.

@Taras

What version of SQL Server are you using behind this?

Kelvin
0
 
Jim Dettman (Microsoft MVP/ EE MVE)Connect With a Mentor President / OwnerCommented:
Just as an aside, I might do this:

    For Each td In CurrentDb.TableDefs
        If td.Name = stLocalTableName Then
            CurrentDb.TableDefs.Delete stLocalTableName
        End If
    Next
     

 Like this:

   On Error Resume Next
   CurrentDb.TableDefs.Delete stLocalTableName
   On Error GoTo AttachDSNLessTable_Err

  While that will be great from a performance stand point (not sure how many tables you have or how often this is called), it does have the down side of masking other errors besides it not being part of the DB already.

  If your worried about that (and I can't think of any reason to be really), then what you should do is this:

   Dim db as DAO.Database

   Set db = CurrentDB()

   For Each td In db.TableDefs
        If td.Name = stLocalTableName Then
            db.TableDefs.Delete stLocalTableName
        End If
    Next

    Your calling currentDB() twice in the loop and that's an expensive call.   The other technique you can use is outlined here:

https://www.experts-exchange.com/articles/2072/CurrentDB-vs-dbEngine-Workspaces-0-Databases-0-and-an-alternative.html

 You'll have to be careful here though because you are manipulating the TableDefs() collection, so you may want to do the:

     Set db = CurrentDB()

 Jim.
0
 
TarasAuthor Commented:
It trigger error at tis line:

Function AttachDSNLessTable(stLocalTableName As String, stRemoteTableName As String, stServer As String, stDatabase As String, Optional stUsername As String, Optional stPassword As String)
    On Error GoTo AttachDSNLessTable_Err
    Dim td As TableDef
    Dim stConnect As String
   
    For Each td In CurrentDb.TableDefs
        If td.Name = stLocalTableName Then
            CurrentDb.TableDefs.Delete stLocalTableName
        End If
    Next
     
    If Len(stUsername) = 0 Then
        '//Use trusted authentication if stUsername is not supplied.
        stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";Trusted_Connection=Yes"
    Else
        '//WARNING: This will save the username and the password with the linked table information.
        stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";UID=" & stUsername & ";PWD=" & stPassword
    End If
    Set td = CurrentDb.CreateTableDef(stLocalTableName, dbAttachSavePWD, stRemoteTableName, stConnect)
    CurrentDb.TableDefs.Append td<<<<<<Error
    AttachDSNLessTable = True
    Exit Function

AttachDSNLessTable_Err:
   
    AttachDSNLessTable = False
    MsgBox "AttachDSNLessTable encountered an unexpected error: " & Err.Description

End Function



SQL Serve is 2012
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
If you set a BreakPoint just before that line, you can examine the contents of your variables. To do that, set the breakpoint and run the procedure. When you hit the breakpoint, use the Immediate window to determine the contents of your variables like this:

?stLocalTableName

And then press the Enter key, and Access will show you the value of that variable. Make sure those values are what you expect.

Also, can you show us exactly how you're calling the function in your code?
0
 
TarasAuthor Commented:
I checked variable with BreakPoint and they has proper value.

I am calling this function two times in  form open event.
First time  is for linking a table another one for view.
First one without second is working it must be something that I need to close connection then open second one.


Call AttachDSNLessTable("My_Access_tblName1", "SQL_Table_Name", "ServerName", "DatabaseName", "", "")
Call AttachDSNLessTable("My_Access_tblName2", "SQL_View_Name", "ServerName", "DatabaseName", "", "")

To avoid problems with view issue I created in SQL server database a table that is identical as that view and I get the same error
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You aren't opening any connections, so you don't need to close anything.

I assume that "SQL_View_Name" is just something you put in the posting here, and in your real-world setup you're actually passing in the name of your View? For example, if the name of my View is "vewCustomers" I'd do this:

Call AttachDSNLessTable("My_Access_tblName2", "vewCustomer", "ServerName", "DatabaseName", "", "")

As ste5an mentioned you may need to preface this with the schema:

Call AttachDSNLessTable("My_Access_tblName2", "dbo.vewCustomer", "ServerName", "DatabaseName", "", "")

Assuming your schema name is "dbo", of course.
0
 
TarasAuthor Commented:
Hi Scot I did exactly as you are suggesting.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Taras,

I didn't really suggest anything in my last comment, so I'm not sure what you're referring to.

Could you please answer the questions posed by me and the other Experts?
0
 
TarasAuthor Commented:
I assume that I do not need to create my local table “My_Access_table2” first then tried to do link??
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
No you don't need to create the local table.

Again, could you please answer our questions? Like the one I posted above:

I assume that "SQL_View_Name" is just something you put in the posting here, and in your real-world setup you're actually passing in the name of your View?
0
 
TarasAuthor Commented:
Scott
“SQL_View_Name” is just name I posted real name is different. I used it with schema name  and it did not work , then I used it without schema name it did not work.

It is interesting I have only two linked tables that I link from Access to SQL Server database in particular schema and this link is working and I can see them in  I tried to connect to another table from same schema that I MS Access Linked table Manager. But if I tried to link another table or view from the same database and same schema I have error.
0
 
Kelvin SparksCommented:
You haven't answered my question regarding the version of SQL Server that you are connecting to.


Kelvin
0
 
TarasAuthor Commented:
Thank you guys for help it was my mistake I was trying to run it against production server instead of developer. On production server I did not have that view.
I will go in corner and slap myself.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.