SQL Server tables - How do I change the actual db it is linked to?

[neemi]

Hi Guys
Please help...

I want to change the below code so that I can change the actual database which the sql tables are linked to... can someone please help on what needs to be done.
Help appreciated.

my current Code below.

On Error GoTo ErrODBC

Dim wsApp As Workspace

Dim dbApp As Database
Dim dbODBC As Database

Dim sODBC As String
Dim iTable As Integer
Dim bOk As Integer
Dim sName As String
Dim tdODBC As TableDef

' Build ODBC connect string
sODBC = "ODBC;"
sODBC = sODBC & "UID=" & sUser & ";"
sODBC = sODBC & "PWD=" & sPassword & ";"
sODBC = sODBC & "DSN=" & sDSN & ";"

' Attempt Connect
Set wsApp = DBEngine.Workspaces(0)
Set dbODBC = wsApp.OpenDatabase("", False, False, sODBC)
Set dbApp = wsApp.Databases(0)

' Attach all non-system tables
iTable = 0
bOk = True
Do While iTable < dbODBC.TableDefs.Count
        
    sName = dbODBC.TableDefs(iTable).Name
        
    ' Format attachment name
    If InStr(1, sName, ".") > 0 Then
        sName = Right(sName, Len(sName) - InStr(1, sName, "."))
    End If

    ' Is it a non-system table
    If dbODBC.TableDefs(iTable).Attributes = 0 And Left(sName, 3) <> "sys" And sName <> "dtproperties" Then
        sName = sPrefix & sName
        g_ODBCAttach = "Attaching " & sName: Debug.Print g_ODBCAttach;
       
On Error Resume Next
        ' Delete existing attachment
        If TableExists(sName) Then
            g_ODBCAttach = String(20 - Len(sName), "."): Debug.Print g_ODBCAttach;
            dbApp.TableDefs.Delete sName
        End If
On Error GoTo ErrODBC

        ' Attach
        g_ODBCAttach = ".": Debug.Print g_ODBCAttach;
        Set tdODBC = dbApp.CreateTableDef(sName, dbAttachSavePWD, dbODBC.TableDefs(iTable).Name, sODBC)
        
        g_ODBCAttach = ".": Debug.Print g_ODBCAttach;
        dbApp.TableDefs.Append tdODBC
        
        g_ODBCAttach = ".Done": Debug.Print g_ODBCAttach
    
    Else
        g_ODBCAttach = "Skipped system table " & sName: Debug.Print g_ODBCAttach
    End If

    iTable = iTable + 1
Loop

Exit Function

ErrODBC:
    MsgBox Error
    ODBCAttach = False
    Exit Function

Thanks so much.
cheers,
neemi

 

[PHV]

What is the value of sDSN ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[neemi]

Thanks for your reply PHV.

It is just set as the DSN name. as would be shown in linked table manager.

ie. tablename (DSN=DSNName; Database=DatabaseName)

but nowhere in my code is specified the database name which is what I need to be able to change?

Cheers,
Neemi

 

[neemi]

Duh!!!

I am so dum!!
It works if I add "Database=sDatabase"!!!

Unless I am missing something.

Cheers,
Neemi