Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2004
    Posts
    72

    Unanswered: connect access mdb via RDO withouit DSN

    How to connect access mdb (without DSN) via connection string?
    Example?

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    I suppose you mean having attached tables without an ODBC link, it's rather easy:
    Code:
    Function AttachServerTables()
    
        Dim tdf As DAO.TableDef
        Dim dbs As DAO.Database
        Dim strCnn As String
        Dim rst As DAO.Recordset
        Dim strSQL As String
    
        ' Connection string for a SQL server using local account:
        strCnn = "ODBC;driver={SQL Server};server=USER-14F7BFF6D6;database=Foundry;uid=SQL_Foundry;pwd=Foundry_123"
        Set dbs = DBEngine.Workspaces(0).Databases(0)
        Set tdf = dbs.CreateTableDef()
        tdf.Name = "MyTable" 'Name of the table in the local database (MDB)
        tdf.SourceTableName = "ServerTableName" ' Name of the table in ther server's database
        tdf.Connect = strCnn
        dbs.TableDefs.Append tdf
        dbs.Close
        Set dbs = Nothing
    
    End Function
    For a list of complete connection strings, see:
    ODBC DSN-Less

    Have a nice day!

  3. #3
    Join Date
    Oct 2004
    Posts
    72
    Quote Originally Posted by Sinndho
    I suppose you mean having attached tables without an ODBC link, it's rather easy:
    Code:
    Function AttachServerTables()
    
        Dim tdf As DAO.TableDef
        Dim dbs As DAO.Database
        Dim strCnn As String
        Dim rst As DAO.Recordset
        Dim strSQL As String
    
        ' Connection string for a SQL server using local account:
        strCnn = "ODBC;driver={SQL Server};server=USER-14F7BFF6D6;database=Foundry;uid=SQL_Foundry;pwd=Foundry_123"
        Set dbs = DBEngine.Workspaces(0).Databases(0)
        Set tdf = dbs.CreateTableDef()
        tdf.Name = "MyTable" 'Name of the table in the local database (MDB)
        tdf.SourceTableName = "ServerTableName" ' Name of the table in ther server's database
        tdf.Connect = strCnn
        dbs.TableDefs.Append tdf
        dbs.Close
        Set dbs = Nothing
    
    End Function
    For a list of complete connection strings, see:
    ODBC DSN-Less

    Have a nice day!
    tkS ... but i need RDO connection and Access mdb not DAO and Sql Server...

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    As far as connecting to an Access database is concerned, there is no problem: simply use a connection string using the Access driver:

    Code:
    "Driver={Microsoft Access Driver (*.mdb)};"Dbq=c:\somepath\mydb.mdb;"SystemDB=c:\somepath\mydb.mdw;"
    It's a bit more difficult to understand why you want to use RDO, though. RDO is a COM wrapper for ODBC. Why don't you use ADO directly?

    Have a nice day!

  5. #5
    Join Date
    Oct 2004
    Posts
    72
    Quote Originally Posted by Sinndho
    As far as connecting to an Access database is concerned, there is no problem: simply use a connection string using the Access driver:

    Code:
    "Driver={Microsoft Access Driver (*.mdb)};"Dbq=c:\somepath\mydb.mdb;"SystemDB=c:\somepath\mydb.mdw;"
    It's a bit more difficult to understand why you want to use RDO, though. RDO is a COM wrapper for ODBC. Why don't you use ADO directly?

    Have a nice day!
    ok....
    But in effect have see ADO is very slow on my lan dir....
    Have an other good solution to speed up connection and variuos operations when i share the mdb access (read, write, select...) on lan server dir?

    have for example a list of "10 gold rules" to share access mdb on lan server dir?

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Here are some links to sites that provide tips for optimizing Access:

    How to optimize Office Access and Jet database engine network performance with Windows 2000-based and Windows XP-based clients
    Increasing MS Access mdb File Operating Speed
    Optimizing Microsoft Office Access Applications Linked to SQL Server

    The third one is about Access and SQL Server but some facts exposed there are also applicable to mdb files.

    When you work with ADO, carefully chose the type of cursor, lock mode etc... can also greatly help performance optimization. Here is an example of what I did to test various configurations for ADO recordsets:
    Code:
    Option Compare Database
    Option Explicit
    
    Private Declare Function GetTickCount Lib "kernel32" () As Long
    Private Const Database = "driver={SQL Server};SERVER=USER-14F7BFF6D6;DATABASE=Sales; Trusted_Connection=yes"
    Private cnnDatabase As ADODB.Connection
    Private rstCF_Data As ADODB.Recordset
    Private ElapsedTime As Long
    
    Function InitializeDataCursors()
    
        Dim TStart As Long
    
        TStart = GetTickCount
        Set cnnDatabase = New ADODB.Connection
        Set rstCF_Data = New ADODB.Recordset
        cnnDatabase.ConnectionString = Database
        cnnDatabase.Open
        With rstCF_Data
    	                                ' try various recordset options
                                                 ' See: ADO Recordset Object
            .CursorLocation = adUseClient   ' adUseServer
            .LockType = adLockOptimistic    ' adLockPessimistic | adLockBatchOptimistic | adLockReadOnly
            .CursorType = adOpenDynamic     ' adOpenForwardOnly | adOpenStatic | adOpenKeyset
            .CacheSize = 30                 ' other values can affect performances
            .Source = "CF_DATA"
           Set .ActiveConnection = cnnDatabase
            .Open "CF_Data", , , , adCmdTableDirect ' adCmdUnspecified | adCmdTable | adCmdUnknown
            .Fields("SysCounter").Properties("Optimize") = True ' as SysCounter is the primary key
        End With
        ElapsedTime = GetTickCount - TStart
        
    #If DEBUG_ON Then
        Debug.Print "InitializeDataCursors"
        Debug.Print "---------------------"
        Debug.Print "Type:", , adOpenDynamic; ""
        Debug.Print "Elapsed time:", , ElapsedTime
        Debug.Print "adAddNew", , rstCF_Data.Supports(adAddNew)
        Debug.Print "adApproxPosition", rstCF_Data.Supports(adApproxPosition)
        Debug.Print "adBookmark", , rstCF_Data.Supports(adBookmark)
        Debug.Print "adFind", , rstCF_Data.Supports(adFind)
        Debug.Print "adHoldRecords", , rstCF_Data.Supports(adHoldRecords)
        Debug.Print "adIndex", , rstCF_Data.Supports(adIndex)
        Debug.Print "adMovePrevious", rstCF_Data.Supports(adMovePrevious)
        Debug.Print "adNotify", , rstCF_Data.Supports(adNotify)
        Debug.Print "adResync", , rstCF_Data.Supports(adResync)
        Debug.Print "adSeek", , rstCF_Data.Supports(adSeek)
        Debug.Print "adUpdate", , rstCF_Data.Supports(adUpdate)
        Debug.Print "adUpdateBatch", , rstCF_Data.Supports(adUpdateBatch)
        Debug.Print
    #End If
    
    End Function
    Again, I was testing for a SQL database but it also works with mdb files (simply change the connection string).

    Coming back to the code I posted to attach tables, it does not mean that you have to work with DAO in your project. It's just a one shot code to create the linked tables. Once those tables are created you can just use any data access model that fits you.

    Finally, if performance is a big issue in your project, you should seriously reconsider using DAO. I know it's obsolete and all but it's the fastest method to work with mdb databases. It is optimized to work with mdb structures while ADO uses a more universal approach so it's more difficult to optimize (why to you think MS Access still uses DAO internally?). In any case, RDO will provide the slowest performance due to the extra layer that encapsulates ADO. About this see:
    Sorting it all Out : What does DAO have that ADO/ADOx/JRO do not?

    Of course, optimizing the database model never hurts. About this, see:
    The Relational Data Model, Normalisation and effective Database Design

    For DAO in general, see:
    FMS Technical Papers - DAO Advanced Programming

    For ADO, some interesting information can be foud at:
    How to find a record using ADO and Jet OLE DB provider
    Universal Data Access Using ADO - Visual Basic Explorer
    ActiveX Data Objects (ADO) Frequently Asked Questions
    Microsoft ActiveX Data Objects (ADO)

    Have a nice day!

  7. #7
    Join Date
    Oct 2004
    Posts
    72
    Quote Originally Posted by Sinndho
    Here are some links to sites that provide tips for optimizing Access:

    How to optimize Office Access and Jet database engine network performance with Windows 2000-based and Windows XP-based clients
    Increasing MS Access mdb File Operating Speed
    Optimizing Microsoft Office Access Applications Linked to SQL Server

    The third one is about Access and SQL Server but some facts exposed there are also applicable to mdb files.

    When you work with ADO, carefully chose the type of cursor, lock mode etc... can also greatly help performance optimization. Here is an example of what I did to test various configurations for ADO recordsets:
    Code:
    Option Compare Database
    Option Explicit
    
    Private Declare Function GetTickCount Lib "kernel32" () As Long
    Private Const Database = "driver={SQL Server};SERVER=USER-14F7BFF6D6;DATABASE=Sales; Trusted_Connection=yes"
    Private cnnDatabase As ADODB.Connection
    Private rstCF_Data As ADODB.Recordset
    Private ElapsedTime As Long
    
    Function InitializeDataCursors()
    
        Dim TStart As Long
    
        TStart = GetTickCount
        Set cnnDatabase = New ADODB.Connection
        Set rstCF_Data = New ADODB.Recordset
        cnnDatabase.ConnectionString = Database
        cnnDatabase.Open
        With rstCF_Data
    	                                ' try various recordset options
                                                 ' See: ADO Recordset Object
            .CursorLocation = adUseClient   ' adUseServer
            .LockType = adLockOptimistic    ' adLockPessimistic | adLockBatchOptimistic | adLockReadOnly
            .CursorType = adOpenDynamic     ' adOpenForwardOnly | adOpenStatic | adOpenKeyset
            .CacheSize = 30                 ' other values can affect performances
            .Source = "CF_DATA"
           Set .ActiveConnection = cnnDatabase
            .Open "CF_Data", , , , adCmdTableDirect ' adCmdUnspecified | adCmdTable | adCmdUnknown
            .Fields("SysCounter").Properties("Optimize") = True ' as SysCounter is the primary key
        End With
        ElapsedTime = GetTickCount - TStart
        
    #If DEBUG_ON Then
        Debug.Print "InitializeDataCursors"
        Debug.Print "---------------------"
        Debug.Print "Type:", , adOpenDynamic; ""
        Debug.Print "Elapsed time:", , ElapsedTime
        Debug.Print "adAddNew", , rstCF_Data.Supports(adAddNew)
        Debug.Print "adApproxPosition", rstCF_Data.Supports(adApproxPosition)
        Debug.Print "adBookmark", , rstCF_Data.Supports(adBookmark)
        Debug.Print "adFind", , rstCF_Data.Supports(adFind)
        Debug.Print "adHoldRecords", , rstCF_Data.Supports(adHoldRecords)
        Debug.Print "adIndex", , rstCF_Data.Supports(adIndex)
        Debug.Print "adMovePrevious", rstCF_Data.Supports(adMovePrevious)
        Debug.Print "adNotify", , rstCF_Data.Supports(adNotify)
        Debug.Print "adResync", , rstCF_Data.Supports(adResync)
        Debug.Print "adSeek", , rstCF_Data.Supports(adSeek)
        Debug.Print "adUpdate", , rstCF_Data.Supports(adUpdate)
        Debug.Print "adUpdateBatch", , rstCF_Data.Supports(adUpdateBatch)
        Debug.Print
    #End If
    
    End Function
    Again, I was testing for a SQL database but it also works with mdb files (simply change the connection string).

    Coming back to the code I posted to attach tables, it does not mean that you have to work with DAO in your project. It's just a one shot code to create the linked tables. Once those tables are created you can just use any data access model that fits you.

    Finally, if performance is a big issue in your project, you should seriously reconsider using DAO. I know it's obsolete and all but it's the fastest method to work with mdb databases. It is optimized to work with mdb structures while ADO uses a more universal approach so it's more difficult to optimize (why to you think MS Access still uses DAO internally?). In any case, RDO will provide the slowest performance due to the extra layer that encapsulates ADO. About this see:
    Sorting it all Out : What does DAO have that ADO/ADOx/JRO do not?

    Of course, optimizing the database model never hurts. About this, see:
    The Relational Data Model, Normalisation and effective Database Design

    For DAO in general, see:
    FMS Technical Papers - DAO Advanced Programming

    For ADO, some interesting information can be foud at:
    How to find a record using ADO and Jet OLE DB provider
    Universal Data Access Using ADO - Visual Basic Explorer
    ActiveX Data Objects (ADO) Frequently Asked Questions
    Microsoft ActiveX Data Objects (ADO)

    Have a nice day!
    Tks for info and link!!!!

    You have a nice day for tath!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •