Solved

How to link A2K tables using UDL or DNS-less (what code and where)

Posted on 2004-03-29
28
469 Views
Last Modified: 2008-01-09
I have an A2K front end database that I want to link to SQL Server 7 backend. I would like to distribute the Access database to multiple users. Each user would open the Access database and have ability to update data through the front end. I have created user ("tester") on SQL database, but would like all Access users to be connected to SQL as "tester" without seeing login screen. This is NOT a web based implementation.

I have seen a UDL file with login and password data that would appear to eliminate the login screen, but don't know how to implement.

[oledb]
; Everything after this line is an OLE DB initstring
Provider=SQLOLEDB.1;Password=password;Persist Security Info=True;User ID=tester;Initial Catalog=Datafile;Data Source=sqlservertest

Is this the correct method, or is there a better way ?  I would like NOT to have to include a special file along with the Access database to the user. (but it may be required)

User OS will be Windows2000

If the method involves adding code - where should the code be placed, and how does it get executed ?

0
Comment
Question by:reprosser
  • 16
  • 12
28 Comments
 
LVL 18

Expert Comment

by:Data-Man
ID: 10706225
here is the connection string that I use...I'm using a SQL Server 2000 database with and Access 2002 frontend

provider=Microsoft.Access.OLEDB.10.0;Data Provider=SQLOLEDB.1;Data Source=sqlservertest;uid=tester;pwd=;database=Datafile

I use this connection string via ADO to reconnect all my tables at startup...what I end up with is are DSN-less linked tables.

Do you need to reconnection at startup?  If not and your database already is setup for dsn-less, then you should be able to use your database without doing anything else.  Unless I'm not understanding what it is you are asking.

Hope this helps
Mike
0
 

Author Comment

by:reprosser
ID: 10707539
Right now I am using a DSN to connect, and I am having problems (tries to connect as the domain login instead of SQL login).

Users will open and close the Access FE multiple times during the day, so I think I need reconnection at startup.

Where do I put the connection string information ? I use the autoexec macro to bring up the first form when the database is opened.
0
 
LVL 18

Expert Comment

by:Data-Man
ID: 10707881
I have the code that I can paste in here later...I'm short on time right now...I store a local table that contains a list of tables that need to be reconnected on startup....will post and explain later tonight.

Mike
0
 

Author Comment

by:reprosser
ID: 10708538
OK - Great. I have 13 linked tables.
0
 
LVL 18

Expert Comment

by:Data-Man
ID: 10708621
create a table called tblLinkedTables....this table is local....it is in the mdb.

Two fields...

TableName text 50 Primary key
Type text 50

Example of data
tblEmployees       ODBC
tblVendors          ODBC


The ODBC is a little misleading....I use it as a flag to tell the code that is comes from SQL Server.
The great thing about this solution is that if you add more tables to SQL Server you need only add the tablename to tblLinkedTables  and the next time you open the database, the table is there.

I'll have the code in a little while....I'm on the East Coast of the US....Eastern Standard Time

Mike
0
 
LVL 18

Accepted Solution

by:
Data-Man earned 250 total points
ID: 10708978
here is the code I was talking about....just copy and paste it into a new module.  It doesn't matter what name you save it with....basStartup is a good name.
May the coding force be with you.....Mike



Option Compare Database
Option Explicit

Sub AutoExec()
   
    If Not MTS_dbDeleteODBCLinkedTables Then


        MsgBox "An error was encounted when trying to delete linked ODBC tables.  Since the deletion and reattachment of these tables is required, this applicaiton will now shut down." & vbnewline & vbnewline & _
               "Please contact your administrator for assistance.", vbCritical, "Could Not Delete ODBC Linked Tables"
        Application.Quit acQuitSaveNone

    End If

    If Not MTS_dbLinkTempTableList Then
        MsgBox "An error was encounted when trying to relink the ODBC tables.  Since these tables are required, the application will now shut down." & vbnewline & vbnewline & _
               "Please contact your administrator for assistance.", vbCritical, "Could Not Relink ODBC Tables"
        Application.Quit acQuitSaveNone
    End If


    'Put your code here to open your switchboard
    Docmd.openform "Switchboard"
   
End Sub


Private Function MTS_dbDeleteODBCLinkedTables() As Boolean
On Error GoTo errHandler

    Dim tdefTemp As DAO.TableDef
    Dim blnDelete As Boolean


    Application.Echo True, "Removing SQL Server Tables...Please Wait"

    'This is in a loop, because sometimes, it wouldn't delete all the tables on the first pass
    Do
        blnDelete = False

        For Each tdefTemp In CurrentDb.TableDefs

            If Left(tdefTemp.Connect, 4) = "ODBC" Then
                CurrentDb.TableDefs.Delete (tdefTemp.Name)
                blnDelete = True
            End If
        Next
    Loop While blnDelete = True

    MTS_dbDeleteODBCLinkedTables = True

exitProc:
    Exit Function

errHandler:
    MTS_dbDeleteODBCLinkedTables = False
 

End Function


Private Function MTS_dbLinkTempTableList() As Boolean
On Error GoTo errHandler

    Dim rstTemp As adodb.Recordset

    Set rstTemp = New adodb.Recordset

    Dim intCurrentCount As Integer

    Application.Echo True, "Linking Required SQL Server Tables....Please Wait"

   
    'Open the table that contains the list of ODBC Tables to be linked

    rstTemp.Open "Select * from tblLinkedTables where Type = 'ODBC'", CurrentProject.Connection, adOpenKeyset, adLockReadOnly

    With rstTemp
        .MoveLast
        .MoveFirst
        Do While Not .EOF
       
            'Keep track of how many tables we are linking
            intCurrentCount = intCurrentCount + 1
            If Not MTS_dbLinkTempTablessa(!TableName, rstTemp.RecordCount, intCurrentCount) Then
                MTS_dbLinkTempTableList = False
                Exit Function
            End If
            .MoveNext
        Loop
    End With

    MTS_dbLinkTempTableList = True

exitProc:
    Exit Function

errHandler:
    MTS_dbLinkTempTableList = False

End Function

Private Function MTS_dbLinkTempTablessa(strTableName As String, intTotalTableCount As Integer, intCurrentTableCount As Integer) As Boolean
On Error GoTo errHandler

    Dim tdefTemp As DAO.TableDef

    Application.Echo True, "Connecting to SQL Server Table  - " & intCurrentTableCount & " of " & intTotalTableCount

    'Link the newly created tables
    Set tdefTemp = CurrentDb.CreateTableDef(strTableName)

    tdefTemp.Connect = "ODBC;Driver=SQL Server;SERVER=ServerNameHere;Database=DatabaseNameHere;uid=testuser;pwd=;"

    'Tell the datbase, which table in SQL Server to link
    tdefTemp.SourceTableName = strTableName

    'Append the newly identified tblDef to this database
    CurrentDb.TableDefs.Append tdefTemp
 
    MTS_dbLinkTempTablessa = True

exitProc:
    Exit Function

errHandler:
    MTS_dbLinkTempTablessa = False

End Function


0
 

Author Comment

by:reprosser
ID: 10714151
Thanks. I created the table and copied the code into a new module.

I get a user defined type error when I try to run the module

Private Function MTS_dbLinkTempTableList() As Boolean
On Error GoTo errHandler

    Dim rstTemp As adodb.Recordset  <--------

I think I have to do something related to ADO - not sure what has to be changed for ADO.
0
 
LVL 18

Expert Comment

by:Data-Man
ID: 10714201
just set a reference to the Microsoft ActiveX Data Objects library.

In the VB Editor open Tools|References

0
 

Author Comment

by:reprosser
ID: 10714816
I checked the references related to ActiveX. I left the DAO reference checked. Will these references travel with the database when I provide it to other users ?

Now if I run the module manually, the tables link.

How do I get the module to run automatically when the database is opened ? There is an AutoExec in the module, but I don't seem to be able to run it from the autoexec macro.

Now that the tables link, the forms load - but I cannot update the data. Will this require ADO changes ?
0
 
LVL 18

Expert Comment

by:Data-Man
ID: 10715042
Yes, the references go where the database goes...the users will have to have ADO installed on their machines.

Create a macro called AutoExec
action - runcode...put the name of the procedure in the argument dbInit()...we should probably chang the autoexec name to dbInit

If you open the database window and a table, can you edit the table, add new records, etc?

If not, then add this procedure to the module and call this proc at the end of the relink.

Public Function MTS_Relink() As Boolean
    'Relink the ODBC Tables linked via ADO
    'The white paper (Q276035), said to refresh the link after the append, which I did
    'in function MTS_dbLinkTempTablessa in this module.  This worked great until
    'I added a password to my user name (Access Security).  See comments in that procedure
    'for details.  I'm using DAO to refresh the link.  I couldn't find an equivelent method
    'in ADO.
    Dim tblTemp As TableDef
    For Each tblTemp In CurrentDb.TableDefs
        If Left(tblTemp.Connect, 4) = "ODBC" Then
            tblTemp.RefreshLink
        End If
    Next
     
End Function

Hope this solves your problem....my database has 60,000 lines of code.

Mike
0
 
LVL 18

Expert Comment

by:Data-Man
ID: 10715169
Did this solve your problem?  if you don't want ADO in your app, then we could probably rewrite the part that uses ADO to use DAO instead.

Mike
0
 

Author Comment

by:reprosser
ID: 10715176
Wow - so much info. Thanks. I increased and awarded the points. I will incorporate your last suggestions and post the results.

If users need to get ado - what file should I send them ?

0
 
LVL 18

Expert Comment

by:Data-Man
ID: 10715247
don't send them a file...have them install mdac from the Microsoft website....do a search for mdac.

Thanks for the points....There is so much to learn about Access and VBA...it can be great fun!!!!

Mike
0
 

Author Comment

by:reprosser
ID: 10715369
I must have syntax wrong - I can't seem to get the autoexec macro to see the (renamed) Sub dbInit(). Should it be a Public Function instead ?

In the autoexec macro I have:

RunCode
Function Name: dbInit()

Generates error - "The expression you entered has a function name that Access can't find"

...It can be great fun. Sometimes too much fun ;-)
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 18

Expert Comment

by:Data-Man
ID: 10715438
yes it has to be a public procedure

0
 

Author Comment

by:reprosser
ID: 10715624
I still cannot edit the data. I can open the linked tables, I can view in design mode (no edit as expected), but I cannot change the data in the tables.

I put the relink in MTS_dbLinkTempTablessa here:

    'Append the newly identified tblDef to this database
    CurrentDb.TableDefs.Append tdefTemp
 
    MTS_dbLinkTempTablessa = True
    MTS_Relink
0
 
LVL 18

Expert Comment

by:Data-Man
ID: 10715690
hmmmm....what about permissions in SQL Server on the table with that user name?  Do they have full access?

what versions of software are you using?


Mike


0
 

Author Comment

by:reprosser
ID: 10715874
Using SQL Server 7 and MSAccess 2002 (with MSAccess 2000 database)

Other users will be using MSAccess 2000.

The user permissions have full access on SQL Server.

If I connect thru my workstation which is logged onto the company domain, when I tr to make changes, I get a beeping noise but no message.

If I connect thru a PC that is not on the domain, when I try to make changes, I see a "Recordset is not updateable" message in the status bar.

Is there a "Trusted connection" parameter in the connect string ?

0
 
LVL 18

Expert Comment

by:Data-Man
ID: 10715934
Try out this website...it has connection strings to just about everything.

http://www.connectionstrings.com/

0
 
LVL 18

Expert Comment

by:Data-Man
ID: 10715945
What OS are you using?
0
 

Author Comment

by:reprosser
ID: 10715946
I just noticed that none of the linked tables show a primary key. Not sure if this is normal for SQL Server linked tables.
0
 

Author Comment

by:reprosser
ID: 10715957
OS is Win2000 Pro
0
 
LVL 18

Expert Comment

by:Data-Man
ID: 10716470
With a primary key, the data is not updateable....check your indexes in SQL Server.

Mike
0
 
LVL 18

Expert Comment

by:Data-Man
ID: 10716488
what data type are some of the Primary keys in SQL Server...do you have clustered indexes on any of the tables?

Mike
0
 

Author Comment

by:reprosser
ID: 10716732
If I go back to the DSN connection to SQL Server, I can log in as tester and update the fields. I can also see the primary key in the tables.

This indicates to me that the permissions are set correctly on SQL, and the database has primary keys on SQL.

Must be something in the ADO connection method that blocks the primary key or something ?
0
 
LVL 18

Expert Comment

by:Data-Man
ID: 10716834
I use the same connection string that I sent you except that I'm using trustedconnection = SSPI

If there are unique constraints, clustered indexes and primary keys, Access doen't know which one to use...I've seen it come across to Access and not have the primary key or have it set to the wrong field.

Mike
0
 

Author Comment

by:reprosser
ID: 10717299
Aughrrr!! - it seems that there were no primary keys in the SQL tables. I am not familiar with SQL Server, but I know when the tables were converted, SQL asked for primary keys - and they were indicated. Must not have actually gotten into the file for some reason. Don't know why they show up when a DSN connect is used...

I added keys, and for now - it all seems to be working. I can open the Access FE and it links to the SQL Server BE and I can edit the data. Now if I can just get the users and roles set up correctly ;-)

Sorry it took so long to nail it down, but thanks Mike for all the help and suggestions.

An A++ perfomance.

rick
0
 
LVL 18

Expert Comment

by:Data-Man
ID: 10717356
Rick....glad I could help out a fellow Access developer....good luck with your application.

Regards,
Mike
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
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…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

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

18 Experts available now in Live!

Get 1:1 Help Now