Access med ODBC til SQL70 Base i flere miljøer

once installed in "miljø2" you need to re-link the linked tables.

It would also be an idea to refresh the links each time you open the database, this way its easy to chnage between a test SQL database and a production database

Sub RefreshLinkX()

    Dim dbsCurrent As Database
    Dim tdfLinked As TableDef

    ' Open a database to which a linked table can be
    ' appended.
    Set dbsCurrent = OpenDatabase("DB1.mdb")

    ' Create a linked table that points to a Microsoft
    ' SQL Server database.
    Set tdfLinked = _
        dbsCurrent.CreateTableDef("AuthorsTable")
    tdfLinked.Connect = _
        "ODBC;DATABASE=pubs;UID=sa;PWD=;DSN=Publishers"
    tdfLinked.SourceTableName = "authors"
    dbsCurrent.TableDefs.Append tdfLinked

' Display contents of linked table.
    Debug.Print _
        "Data from linked table connected to first source:"
    RefreshLinkOutput dbsCurrent

    ' Change connection information for linked table and
    ' refresh the connection in order to make the new data
    ' available.
    tdfLinked.Connect = _
        "ODBC;DATABASE=pubs;UID=sa;PWD=;DSN=NewPublishers"
    tdfLinked.RefreshLink

    ' Display contents of linked table.
    Debug.Print _
        "Data from linked table connected to second source:"

RefreshLinkOutput dbsCurrent

    ' Delete linked table because this is a demonstration.
    dbsCurrent.TableDefs.Delete tdfLinked.Name

    dbsCurrent.Close

End Sub

Sub RefreshLinkOutput(dbsTemp As Database)

    Dim rstRemote As Recordset
    Dim intCount As Integer

    ' Open linked table.
    Set rstRemote = _
        dbsTemp.OpenRecordset("AuthorsTable")

    intCount = 0

    ' Enumerate Recordset object, but stop at 50 records.
    With rstRemote
        Do While Not .EOF And intCount < 50

Debug.Print , .Fields(0), .Fields(1)
            intCount = intCount + 1
            .MoveNext
        Loop
        If Not .EOF Then Debug.Print , "[more records]"
        .Close
    End With

End Sub

Prøv nedenstående kode!

Den kaldes således:
call Sammenkæd_Tabeller("Navn på en af dine tabeller")


Her er koden:
Public Function Sammenkæd_Tabeller(Tbl As String) As Boolean
    On Error GoTo Err_reattach
    Dim db As Database
    Dim tdef As TableDef, qdef As QueryDef
    Dim ConStr As String, OldConStr As String
    Dim ConnectString As String, tblSourceName As String, tblName As String, n As Integer
    Set db = CurrentDb
    Set tdef = db.TableDefs(Tbl)
    tblSourceName = tdef.SourceTableName
    ConStr = tdef.Connect
   
    If ConStr = "" Then
        Sammenkæd_Tabeller = False
        Exit Function
    End If
    DoCmd.Hourglass True
    For Each tdef In db.TableDefs
        '<<----Hvis tabellen er tilknyttet---->>
        If Left(tdef.Connect, 5) = "ODBC;" Then
            tblName = tdef.Name
            tdef.Connect = ConStr
            tdef.RefreshLink
        End If
    Next tdef
   
    DoCmd.Hourglass False
    Sammenkæd_Tabeller = True
    Exit Function
Err_reattach:
    DoCmd.Hourglass False
    Select Case Err
        Case 3011:
            If MsgBox("Table " & tblName & " wasn't reattached@@Do you wish to continue reattaching tables?", vbQuestion + vbYesNo, "Continue?") = vbNo Then
                Sammenkæd_Tabeller = False
                Exit Function
            End If
        Case Else
            If MsgBox(Err.Description & vbNewLine & tblName & vbNewLine & tblSourceName & "@@Do you wish to continue reattaching tables?", vbQuestion + vbYesNo, "Continue?") = vbNo Then
                Sammenkæd_Tabeller = False
                Exit Function
            End If
    End Select
    DoCmd.Hourglass True
    Resume Next
End Function


/Thomas

Denne kode gennemløber alle dine sammenkædedet tabeller og tilknytter dem til den DNS, som du vælger.

Til terry:

That code gives me error..
1) DB1.mdb not found

Til Thomasjepsen:
Den ser ud til at virke, dog må den IKKE poppe op med noget som helst, og det gør den desværre. Den:

1) Spørger efter Bruger ID og Password til den gamle "ikke eksisterende" server.

2) Skriver at den ikke kunne attache nogle tabeller...

What to do???

/Jesper

dbi1.mdb is the name of your database!

dbi1  = db1

you can change Set dbsCurrent = OpenDatabase("DB1.mdb")

to
Set dbsCurrent = Currentdb

Lad os lige få noget på det rene: Du har 2 maskiner, som skal trække på 2 forskellige SQL servere?
Du siger at ODBC peger på den aktuelle server? Med ODBC, mener du da din Datakilde i ODBC-indstillinger i kontrolpanelet?
Den har nemlig ikke direkte noget at gøre med hvor tabellerne peger hen. Access bruger kun ODBC datakilderne til at guide sig vej. Herefter gemmer den selv en connectstreng.

Hvis du trykker Ctrl+G og skriver:
?currentdb.TableDefs("En Af dine tabeller").Connect

så ser du din connectstring på den pågældende maskine.

thomas: Rigtigt, rigtigt og rigtigt.... Det er det problem jeg skal ud over...

You can also open the table in design view then right click and choose properties from the menu. If you now look at the description you will see the connection string Access is using to try and attach to the dB. This will show the string used from "miljø 1", and this is why you need to refresh the connection string.

Prøv denne kode:

Public Sub Attach(ConStr As String)
    On Error GoTo Err_reattach
    Dim db As Database, tblName As String
    Dim tdef As TableDef, qdef As QueryDef
    Set db = CurrentDb
   
    If ConStr = "" Then
        Exit Sub
    End If
    DoCmd.Hourglass True
   
    For Each tdef In db.TableDefs
        '<<----Hvis tabellen er tilknyttet---->>
        If Left(tdef.Connect, 5) = "ODBC;" Then
            tblName = tdef.Name
            tdef.Connect = ConStr
            tdef.RefreshLink
        End If
    Next tdef
    For Each qdef In db.QueryDefs
        If qdef.Connect <> "" Then
            qdef.Connect = ConStr
        End If
    Next qdef
    DoCmd.Hourglass False
    Exit Sub
Err_reattach:
    DoCmd.Hourglass False
    Select Case Err
        Case 3011:
            If MsgBox("Table " & tblName & " wasn't reattached@@Do you wish to continue reattaching tables?", vbQuestion + vbYesNo, "Continue?") = vbNo Then
                Exit Sub
            End If
        Case Else
            If MsgBox(Err.Description & vbNewLine & tblName & "@@Do you wish to continue reattaching tables?", vbQuestion + vbYesNo, "Continue?") = vbNo Then
                Exit Sub
            End If
    End Select
    DoCmd.Hourglass True
    Resume Next
End Sub

Du skal således have fat i de 2 connect-strenge.

På hver maskine kalder du så f.eks. denne:
Attach "ODBC;DRIVER=SQL Server;SERVER=SQLservery;APP=Microsoft® Access;DATABASE=VKD;LANGUAGE=Dansk"

øh.... Jeg har ikke disse connect strenge.....

Da jeg ikke på forhånd har Miljø 2 fast defineret, det hele skal gøres med en unattended installation. ALT skal findes automatisk og ALT skal rettes automatisk.. DVS lægges ind i AUTOEXEC og startes hver gang basen røres...

Du kan jo ikke connecte hvis du slet ikke ved hvad serveren eller databasen hedder!?

Du kan heller ikke stille dig tilfreds med at brugeren skal indtaste bare Server-navnet? For så kan du jo selv sammensætte connectstrengen, hvis alt det andet er fastdefineret.

Serveren bliver defineret i ODBC forbindelsen. Det skulle vel være nok....

og nej, brugeren skal/må ikke indtaste noget, det skal være unattended

Men...ODBC-forbindelsen er jo netop den streng, som jeg har refereret til flere gange ("ODBC;DRIVER=SQL Server;SERVER=SQLservery;APP=Microsoft® Access;DATABASE=VKD;LANGUAGE=Dansk")

Du kan definere en ODBC datakilde, som peger på databasen, men så kræver det jo at brugeren har lavet denne datakilde (DSN). Og for at kunne oprette den DSN, så skal man angive servernavn, database samt brugernavn/passwrd.

bare fordi, at du har en SQL server, har du ikke automatisk en ODBC datakilde, som peger på den.

Thomas: Jeg er ikke dum.......
Den ODBC-kilde streng kender jeg dog ikke noget til, men ODBC-forbindelsen i NT er lavet op mod SQL-serveren. doh...

wizax, det var overhovedet ikke nogen hentydning til at du var dum !! Det er jeg ked af, hvis du opfattede det sådan!

Jeg har bare tidligere oplevet, at folk troede at bare der var en SQL server installeret, så var der også en ODBC/DSN-forbindelse dertil.

Jeg er ikke helt med på, hvad du mener med at "ODBC-forbindelsen i NT er lavet op mod SQL serveren"?

Jeg mener, for at skære det ud i pap, at man har på forhånd været indi i kontrol-panelet under ODBC-forbindelser og oprette den pågældende forbindelse.

wizax>This example will also create the ODBC data source if it doesnt already exist.

Create a new database called TestODBC.mdb.


Create the following table to store SQL Server ODBC data source information. You can modify the fields in this table to store the DSN information for any ODBC driver:


Table: tblODBCDataSources
Field Name Data Type Field Size

DataBase Text 50
UID Text 50
PWD Text 50
Server Text 50
ODBCTableName Text 50
LocalTableName Text 50
DSN Text 50


Make the LocalTableName field the primary key, and save the table as tblODBCDataSources.


Create a record in the tblODBCDataSources table with information about your ODBC databases. This example uses a SQL Server connection to the Authors table in the Pubs database; substitute the correct information for your environment, and add a record for each linked table in your database:

Field Name Value
Database Pubs
UID sa
PWD <blank>
Server SQLPUBS
ODBCTableName dbo.authors
LocalTableName Authors
DSN Pubs


Create a module and type the following line in the Declarations section if it is not already there:


Option Explicit
Type the following procedures:

NOTE: The sample code in this article uses Microsoft Data Access Objects. For this code to run properly, you must reference the Microsoft DAO 3.6 Object Library. To do so, click References on the Tools menu in the Visual Basic Editor, and ensure that the Microsoft DAO 3.6 Object Library check box is selected.


'***************************************************************
'The DoesTblExist function validates the existence of a TableDef
'object in the current database. The result determines if an
'object should be appended or its Connect property refreshed.
'***************************************************************
Function DoesTblExist(strTblName As String) As Boolean
  On Error Resume Next
  Dim db As DAO.Database, tbl As DAO.TableDef
  Set db = CurrentDb
  Set tbl = db.TableDefs(strTblName)
  If Err.Number = 3265 Then  ' Item not found.
      DoesTblExist = False
      Exit Function
  End If
  DoesTblExist = True
End Function

Function CreateODBCLinkedTables() As Boolean
  On Error GoTo CreateODBCLinkedTables_Err
  Dim strTblName As String, strConn As String
  Dim db As DAO.Database, rs As DAO.Recordset, tbl As DAO.TableDef
  ' ---------------------------------------------
  ' Register ODBC database(s).
  ' ---------------------------------------------
  Set db = CurrentDb
  Set rs = db.OpenRecordset("tblODBCDataSources")
  With rs
      While Not .EOF
        DBEngine.RegisterDatabase rs("DSN"), _
                  "SQL Server", _
                  True, _
                  "Description=VSS - " & rs("DataBase") & _
                  Chr(13) & "Server=" & rs("Server") & _
                  Chr(13) & "Database=" & rs("DataBase")
        ' ---------------------------------------------
        ' Link table.
        ' ---------------------------------------------
        strTblName = rs("LocalTableName")
        strConn = "ODBC;"
        strConn = strConn & "DSN=" & rs("DSN") & ";"
        strConn = strConn & "APP=Microsoft Access;"
        strConn = strConn & "DATABASE=" & rs("DataBase") & ";"
        strConn = strConn & "UID=" & rs("UID") & ";"
        strConn = strConn & "PWD=" & rs("PWD") & ";"
        strConn = strConn & "TABLE=" & rs("ODBCTableName")
        If (DoesTblExist(strTblName) = False) Then
            Set tbl = db.CreateTableDef(strTblName, _
                          dbAttachSavePWD, rs("ODBCTableName"), _
                          strConn)
            db.TableDefs.Append tbl
        Else
            Set tbl = db.TableDefs(strTblName)
            tbl.Connect = strConn
            tbl.RefreshLink
        End If

        rs.MoveNext
      Wend
  End With
  CreateODBCLinkedTables = True
  MsgBox "Refreshed ODBC Data Sources", vbInformation
CreateODBCLinkedTables_End:
  Exit Function
CreateODBCLinkedTables_Err:
  MsgBox Err.Description, vbCritical, "MyApp"
  Resume CreateODBCLinkedTables_End
End Function

To test this function, type the following line in the Immediate window, and then press ENTER:

?CreateODBCLinkedTables()

Note that you receive the message "Refreshed ODBC Data Sources." Also note that a new linked table called Authors exists in your database, and a new User DSN called Pubs exists in the ODBC Administrator in Control Panel.

wizax>Are you still needinghelp with this?

Hi Terry... Thanks for the attempts of heling me, but I feel like you and Thomas do not understand my problem completely....

1) The links must be reconnected in AUTOEXEC.
2) NO POPUPS/MESSAGES/TEXT may be shown, and NO user intervention.
3) The ODBC-links are defined in NT, and SOULD NOT BE CREATED.
4) The solution MUST be generic, so It will reconnect ALL links. If possible search for the tables itself.

Sorry if I sound demanding or rude, but point 1-3 has already been pointed out (several times), and they are _essential_ for the implementation of this fix.

remark: I have NO knowledge of code in Access, and therefore I cannot make adjustments to the code delivered by you....

Hi again wizax
If you have NO knowledge of code in Access then HOW do you expect to implement ANY solution, do you expect it to work as is?

What about user name and password isnt theuser supposed to get prompted for this?
Also, what about the database, can the name of this change?

Wizax>This DOES work with an existing ODBC data source and NO PROMPTS.

If you are using NT logins then you should remove the two lines which look like this
    strConn = strConn & "UID=" & gsUserName & ";"
    strConn = strConn & "PWD=" & gsPassword & ";"
Then you can make a call to the function MyRefreshLinks() from autoexec.

If you are need to enter a user name and password then you need to make a login form. Then set the two variables gsUserName and gsPassword and then call the function MyRefreshLinks()


In BOTH cases you need to enter the DSN and table names in the table tblODBCDataSources.




Copy the following into a Module


Global gsUserName As String
Global gsPassword As String


'***************************************************************
'The DoesTblExist function validates the existence of a TableDef
'object in the current database. The result determines if an
'object should be appended or its Connect property refreshed.
'***************************************************************
Function DoesTblExist(strTblName As String) As Boolean
  On Error Resume Next
  Dim db As DAO.Database, tbl As DAO.TableDef
  Set db = CurrentDb
  Set tbl = db.TableDefs(strTblName)
  If Err.Number = 3265 Then  ' Item not found.
      DoesTblExist = False
      Exit Function
  End If
  DoesTblExist = True
End Function


Function MyRefreshLinks() As Boolean
  On Error GoTo MyRefreshLinks_Err
  Dim strTblName As String, strConn As String
  Dim db As DAO.Database, rs As DAO.Recordset, tbl As DAO.TableDef
  ' ---------------------------------------------
  ' Register ODBC database(s).
  ' ---------------------------------------------
  Set db = CurrentDb
  Set rs = db.OpenRecordset("tblODBCDataSources")
  With rs
      While Not .EOF
          strTblName = rs("LocalTableName")
        strConn = "ODBC;"
        strConn = strConn & "DSN=" & rs("DSN") & ";"
        strConn = strConn & "APP=Microsoft Access;"

        strConn = strConn & "UID=" & gsUserName & ";"
        strConn = strConn & "PWD=" & gsPassword & ";"
       
        strConn = strConn & "TABLE=" & rs("ODBCTableName")
        If (DoesTblExist(strTblName) = False) Then
            Set tbl = db.CreateTableDef(strTblName, _
                          dbAttachSavePWD, rs("ODBCTableName"), _
                          strConn)
            db.TableDefs.Append tbl
        Else
            Set tbl = db.TableDefs(strTblName)
            tbl.CONNECT = strConn
            tbl.RefreshLink
        End If

        rs.MoveNext
      Wend
  End With
  MyRefreshLinks = True
 
MyRefreshLinks_End:
  Exit Function
MyRefreshLinks_Err:
  msgBox Err.Description, vbCritical, "MyApp"
  Resume MyRefreshLinks_End
End Function

Terry:
I can copy and Paste stuff, and I have frinds that have told me how to make a Module (Briefly) what else do I need?

Usrname and PWD is in the ODBC-connection defined, so there is no entering UID or PWD, but using the one that is already logged on.

Morning!

If you are logging on with NT permissions then all you need to do is this>


Create a table.

Table: tblODBCDataSources

Field Name, Data Type, and  Field Size

ODBCTableName Text 50
LocalTableName Text 50
DSN Text 50


and then make the LocalTableName field the primary key, and save the table as tblODBCDataSources


Then enter the information about your tables for example.

ODBCTableName  = TableNameInSQLServer
LocalTableName = tblMyLinkedTable
DSN = DSNName

You do this for ALL tables you want to link!



Then you make a macro named autoexec and in the Action field you choose RunCode and in the Function Name field you enter
=MyRefreshLinks()

Then copy the following into a new Module WITHOUT any changes.




'***************************************************************
'The DoesTblExist function validates the existence of a TableDef
'object in the current database. The result determines if an
'object should be appended or its Connect property refreshed.
'***************************************************************
Function DoesTblExist(strTblName As String) As Boolean
  On Error Resume Next
  Dim db As DAO.Database, tbl As DAO.TableDef
  Set db = CurrentDb
  Set tbl = db.TableDefs(strTblName)
  If Err.Number = 3265 Then  ' Item not found.
      DoesTblExist = False
      Exit Function
  End If
  DoesTblExist = True
End Function


Function MyRefreshLinks() As Boolean
  On Error GoTo MyRefreshLinks_Err
  Dim strTblName As String, strConn As String
  Dim db As DAO.Database, rs As DAO.Recordset, tbl As DAO.TableDef
  ' ---------------------------------------------
  ' Register ODBC database(s).
  ' ---------------------------------------------
  Set db = CurrentDb
  Set rs = db.OpenRecordset("tblODBCDataSources")
  With rs
      While Not .EOF
          strTblName = rs("LocalTableName")
        strConn = "ODBC;"
        strConn = strConn & "DSN=" & rs("DSN") & ";"
        strConn = strConn & "APP=Microsoft Access;"       
        strConn = strConn & "TABLE=" & rs("ODBCTableName")
        If (DoesTblExist(strTblName) = False) Then
            Set tbl = db.CreateTableDef(strTblName, _
                          dbAttachSavePWD, rs("ODBCTableName"), _
                          strConn)
            db.TableDefs.Append tbl
        Else
            Set tbl = db.TableDefs(strTblName)
            tbl.CONNECT = strConn
            tbl.RefreshLink
        End If

        rs.MoveNext
      Wend
  End With
  MyRefreshLinks = True
 
MyRefreshLinks_End:
  Exit Function
MyRefreshLinks_Err:
  msgBox Err.Description, vbCritical, "MyApp"
  Resume MyRefreshLinks_End
End Function

Forgot to say that you MUST have a reference to DAO you can do this in Tools+References while in code view.

Then find Microsoft DAO 3.x Object Library and select it.
Hopefully that should be enough!

wizax>hows it going, have you got it to work yet?
If you are having problems implementing this then if you give me th einformation of which tables you have in the dB AND the DSN name then I will implement it for you íf you send me your database
terry@santhell.dk

wizax have you gone on holiday?

wizax can you please responde to this question!

Sorry.... My comp was down with a bad disk....
I haven't tried this last solution yet, but I guess it will work.....
thanks for the help!!!

Hope it does, if not then let me know, and thanks :o)