Solved

MS Access AttachDSNLessTable

Posted on 2016-08-09
21
59 Views
Last Modified: 2016-09-14
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
0
Comment
Question by:Taras
  • 7
  • 5
  • 5
  • +3
21 Comments
 
LVL 22

Assisted Solution

by:Kelvin Sparks
Kelvin Sparks earned 62 total points
ID: 41749426
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
 
LVL 32

Assisted Solution

by:ste5an
ste5an earned 62 total points
ID: 41749432
Also check whether you're using the correct schema prefix.
0
 

Author Comment

by:Taras
ID: 41749436
schema prefix is the same,  permission  are the same on objects.
0
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 41749451
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
 
LVL 3

Assisted Solution

by:bfuchs
bfuchs earned 62 total points
ID: 41749497
@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
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 41749504
@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
 
LVL 3

Expert Comment

by:bfuchs
ID: 41749516
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
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 41749526
@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
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 252 total points
ID: 41750166
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
 
LVL 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 62 total points
ID: 41750193
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:Taras
ID: 41750279
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
 
LVL 84
ID: 41750380
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
 

Author Comment

by:Taras
ID: 41750599
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
 
LVL 84
ID: 41750638
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
 

Author Comment

by:Taras
ID: 41750774
Hi Scot I did exactly as you are suggesting.
0
 
LVL 84
ID: 41750812
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
 

Author Comment

by:Taras
ID: 41750819
I assume that I do not need to create my local table “My_Access_table2” first then tried to do link??
0
 
LVL 84
ID: 41750828
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
 

Author Comment

by:Taras
ID: 41750866
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
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 41750882
You haven't answered my question regarding the version of SQL Server that you are connecting to.


Kelvin
0
 

Author Comment

by:Taras
ID: 41751019
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

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

758 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

21 Experts available now in Live!

Get 1:1 Help Now