• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 559
  • Last Modified:

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

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
reprosser
Asked:
reprosser
  • 16
  • 12
1 Solution
 
Data-ManCommented:
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
 
reprosserAuthor Commented:
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
 
Data-ManCommented:
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
reprosserAuthor Commented:
OK - Great. I have 13 linked tables.
0
 
Data-ManCommented:
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
 
Data-ManCommented:
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
 
reprosserAuthor Commented:
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
 
Data-ManCommented:
just set a reference to the Microsoft ActiveX Data Objects library.

In the VB Editor open Tools|References

0
 
reprosserAuthor Commented:
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
 
Data-ManCommented:
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
 
Data-ManCommented:
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
 
reprosserAuthor Commented:
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
 
Data-ManCommented:
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
 
reprosserAuthor Commented:
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
 
Data-ManCommented:
yes it has to be a public procedure

0
 
reprosserAuthor Commented:
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
 
Data-ManCommented:
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
 
reprosserAuthor Commented:
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
 
Data-ManCommented:
Try out this website...it has connection strings to just about everything.

http://www.connectionstrings.com/

0
 
Data-ManCommented:
What OS are you using?
0
 
reprosserAuthor Commented:
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
 
reprosserAuthor Commented:
OS is Win2000 Pro
0
 
Data-ManCommented:
With a primary key, the data is not updateable....check your indexes in SQL Server.

Mike
0
 
Data-ManCommented:
what data type are some of the Primary keys in SQL Server...do you have clustered indexes on any of the tables?

Mike
0
 
reprosserAuthor Commented:
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
 
Data-ManCommented:
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
 
reprosserAuthor Commented:
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
 
Data-ManCommented:
Rick....glad I could help out a fellow Access developer....good luck with your application.

Regards,
Mike
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

  • 16
  • 12
Tackle projects and never again get stuck behind a technical roadblock.
Join Now