ODBC og ACCESS

er det ikke bare at lave et vb script hvor du har en

case databasevalg1

odbc....

case databasevalg2

odbc....

->seider< Jow men jeg er også ude efter de magiske besværgelser der gør som beskrevet ;o)

Place the following in a module.

Public Declare Function ShellExecute Lib \"shell32.dll\" Alias \"ShellExecuteA\" (ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long

Public Const SW_HIDE = 0
Public Const SW_SHOW = 5

Then in a form the following...
Dim result As Integer
Dim stAppPath As String
Dim stAppName As String

Dim tdef As TableDef
Dim db As Database

    stAppPath = \"C:\\\"

  If ... Then ...
    stAppName = \"Reg1.REG\"
  else
    stAppName = \"Reg2.REG\"
  endif

    result = ShellExecute(0, \"Open\", stAppPath & stAppName, stAppPath, \"\", SW_SHOW)

    Set db = CurrentDb
    Set tdef = db.TableDefs(\"tblYourTable\")
    tdef.RefreshLink

The reg files should lokk something like this

REGEDIT4

[HKEY_LOCAL_MACHINE\\SOFTWARE\\ODBC\\ODBC.INI\\ODBC Data Sources]
\"Building Maintenance\"=\"SQL Server\"

[HKEY_LOCAL_MACHINE\\SOFTWARE\\ODBC\\ODBC.INI\\MY DSN]
\"Driver\"=\"C:\\\\WINNT\\\\System32\\\\sqlsrv32.dll\"
\"Description\"=\"DSN for Myapp\"
\"Server\"=\"Your server\"
\"Database\"=\"Your dB\"
\"Language\"=\"us_english\"
\"LastUser\"=\"\"
\"AutoTranslate\"=\"No\"

This information can be found in the registry

Oh! you were using Oracle, so its a bit different.

Your .REG files should look something like this>
REGEDIT4

[HKEY_LOCAL_MACHINE\\SOFTWARE\\ODBC\\ODBC.INI\\ODBC Data Sources]
\"TEST DS\"=\"Oracle73\"

[HKEY_LOCAL_MACHINE\\SOFTWARE\\ODBC\\ODBC.INI\\TEST DS]
\"Driver\"=\"C:\\\\ORANT\\\\ODBC\\\\sqo32_73.dll\"
\"Description\"=\"MY Test\"
\"Server\"=\"Your Server\"
\"AsyncSupport\"=\"Disabled\"

These can be found in the registry key HKEY_LOCAL_MACHINE or HKEY_CURRRENT_USER depending on whether or not its a user or system data source.

You obviously need to alter the If then .. to suit your application in could maybe be a radio button group where you select one or the other database. In both reg files the information is exactly the same, appart from the Databasename and/or Server.
It would maybe also be an idea to ensure that you link to the same database each time the application starts, and set the radio buttons accordingly. Get the idea??

Hey Hein,

Følgende kode erstatter connectstring for både tabeller og forespørgsler (hvis du har videregivelsesforespørgsler!). Koden lægges evt i et Modul.

Proceduren benytter DAO-teknik, så hvis du bruger Access 2000/XP skal du lave en reference til \"Microsoft DAO 3.6 Object Library\"

Public Sub SkiftAttachment(ConStr As String)
  Dim tdef As TableDef, qdef As QueryDef
  Dim TblName As String
  Dim db As Database
  Set db = CurrentDb
  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
End Sub

Herefter kan koden kaldes med de 2 forskellige connectstrings.
Eks:
SkiftAttachment \"ODBC;DRIVER=SQL Server;SERVER=(local);UID=sa;PWD=tja;DATABASE=MinDatabase\"

Ovenstående eksempel er godt nok til en SQL server, men hvis du har connectstring\'en til Oracle, så er det bare at erstatte den.

mvh
Thomas

Du kan rent kodemæssigt vælge ODBC forbindelse. Her er et eksempel hvor jeg forbinder til en odbc som jeg kalder tmatest:

    Dim wrkODBC As Workspace, conPubs As Connection
    Set wrkODBC = CreateWorkspace(\"NewODBCWorkspace\", \"admin\", \"\", dbUseODBC)
    Set conPubs = wrkODBC.OpenConnection(\"tmatest\", dbDriverNoPrompt, False, \"ODBC;UID=nn;PWD=nn;DSN = tmatest;\")
   
    conPubs.Execute \"delete from promis.ep_items where orderno = \'61477\'\"
    MsgBox \"Antal slettet \" & conPubs.RecordsAffected
   
    conPubs.Close
    wrkODBC.Close
    Set conPubs = Nothing
    Set wrkODBC = Nothing

Du kunne så blot i koden vælge hvilken af de to du skal benytte (du skal selvfølgelig oprette de to)

Hilsen Torben

HEIN: Er du der stadig?

Hvilken version af Access benytter du?

Hvis Access 2000, er det så et Data Project?

/Thomas

(...rettelse)

Det er selvfølgelig ikke et Data Project, hvis du benytter ODBC og Oracle...dumme mig ;o)

Hej Alle
Tak for de mange svar jeg vender tilbage med pointfordelingen når jeg har valgt en af løsningerne...

:o)