Results 1 to 3 of 3
  1. #1
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5

    Unanswered: Need code to create dsn-less connection based names of tables in local table

    I have tried evry snippet of code I have seen to make a dsnless connection - and i am thinking it is my machine - the following code works on other clients i have but i cannot get it to run - it says
    operation is not supported for this type of object


    Public Function ConnectDataTables()

    On Error GoTo myerrors

    Dim rst As Recordset, mytabledef As New DAO.TableDef, mystring As String, strtablesource As String
    Dim dbcurrent As DAO.Database
    Dim strconnection As String
    strconnection = "DRIVER={SQL Server};UID = djones;Database = test;WSID = VCWK72;APP=2007 Microsoft Office system;Trusted_Connection = Yes;Server = myserver"
    Set dbcurrent = DBEngine.Workspaces(0).Databases(0)
    Debug.Print strconnection
    Set rst = CurrentDb.OpenRecordset("MSysDataTables")
    While Not rst.EOF
    If Left$(rst!TableName, 4) = "dbo_" Then
    strtablesource = rst!TableName
    Set mytabledef = CurrentDb.CreateTableDef(strtablesource, , strtablesource, strconnection)
    mytabledef.Connect = strconnection
    mytabledef.SourceTableName = strtablesource
    dbcurrent.TableDefs.Append (mytabledef)
    CurrentDb.TableDefs.Refresh
    End If
    rst.MoveNext
    Wend
    rst.Close
    Dale Houston, TX

  2. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    Why note just use the link table that what I did
    The way I learnt I created a macro first then got msaccess to convert to vb then I just change the bit I needed
    Last edited by myle; 02-09-11 at 04:11.
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  3. #3
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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