?
Solved

Access 97 linking databases

Posted on 2007-07-25
7
Medium Priority
?
376 Views
Last Modified: 2008-01-09
hello all,

Just linking a tables in access for some users to use.  The table is in a sysbase server.  I want to link the tables to the sysbase server in Access and not have the users have to enter in the user id and password everytime they open the tables.
I am using access 97

Thanks for the help
0
Comment
Question by:Oscar Rodriguez
7 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 19566659
In addition to mx's links, this one shows connection strings specific to Sybase:

http://www.connectionstrings.com/?carrier=sybase
0
 
LVL 3

Accepted Solution

by:
atherh earned 2000 total points
ID: 19566674
'Copy and paste this function into module

'==========================================================

Public 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

'change the setup dsn according to your server information
'==========================================================

AttachDSNLessTable "LocalTable Name", "SybaseTable Name", "SybaseServer Name ", "DatabaseName", "SybaseUserName", "Sybase Password"
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 3

Expert Comment

by:atherh
ID: 19566780
'and make in main function AttachDSNLessTable
DRIVER=Sybase SQL Anywhere 5.0

0
 

Author Comment

by:Oscar Rodriguez
ID: 19568260
I cant figure it out...   The name of my linked table is called dbo_counterparty

Am i supposed to reference this table somewhere in the code...  
0
 
LVL 3

Expert Comment

by:atherh
ID: 19572681
AttachDSNLessTable "dbo_counterparty", "dbo_counterparty", "SybaseServer Name ", "DatabaseName", "SybaseUserName", "Sybase Password"



0
 
LVL 3

Expert Comment

by:atherh
ID: 19572707



'Copy and paste this function into module

'==========================================================

Public 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=Sybase SQL Anywhere 5.0
;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=Sybase SQL Anywhere 5.0
;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


Public sub setupDSN()
AttachDSNLessTable "dbo_counterparty", "dbo_counterparty", "SybaseServer Name ", "DatabaseName", "SybaseUserName", "Sybase Password"

end sub

just run the setupDSN in immidiate window ( by Pressing Ctrl+G ) and every thing will be setup for u.
later on you can put on startup.
I havent made the changes for

database
server name
user name
password

hope you can understand it now.
the good thing about this is if u change the server or any thing u jsut need to make changes again and run the setupDSN .
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article, we’ll look at how to deploy ProxySQL.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

807 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