Solved

Can I change a Linked Table with VB?

Posted on 2003-11-20
5
564 Views
Last Modified: 2008-02-01
I have a database that is linked to an SQL server via FileDSN.  However, my FileDSN is 'slightly' different from my users'.  I thought I could put a version of their FileDSN and a version of mine in the same folder that gets distributed with my application's updates.  Temporarily I name their File "FileDSN1" and use my own "FileDSN" to link the tables to the server.

Then before distributing the update, I could swap the file names and let their file be named "FileDSN" so the linked tables would work properly for them....

THE PROBLEM IS:
The table description holds the information from "my" file DSN because that is the one used to create the links.

CAN ANYONE TELL ME HOW TO:
Create a linked table with VB; using a specific fileDSN?
//or//
Change the properties on the Linked tables that are being distributed?

Not though VB, but just opening the linked tables normally, it tells me I can not save those changes.  It seems like it might just be easier to create the links with VB, but maybe not.

0
Comment
Question by:Y2Chris75
  • 3
  • 2
5 Comments
 
LVL 3

Expert Comment

by:Drizzt95
Comment Utility
You have two options that I can see.

1.  Relink the tables when the database is first open pointing to the new fileDSN using VBA.

2. Use a system or user DSN and set Access to create the DSN when the database first runs.

I personally use option 2 on all my remote applications.  It first detects if the DSN exists, and if it doesn't it creates it.  It is very quick and seamless, the users won't even realise it's happening.  I'll post the code just in case you like the idea.



'@@@    REGISTRY DECLARATIONS
Private Declare Function RegCreateKey Lib "advapi32.dll" Alias _
    "RegCreateKeyA" (ByVal hKey As Long, ByVal lpSubKey As String, _
    phkResult As Long) As Long

Private Declare Function RegSetValueEx Lib "advapi32.dll" Alias _
    "RegSetValueExA" (ByVal hKey As Long, ByVal lpValueName As String, _
    ByVal Reserved As Long, ByVal dwType As Long, lpData As Any, ByVal _
    cbData As Long) As Long

Private Declare Function RegCloseKey Lib "advapi32.dll" _
    (ByVal hKey As Long) As Long
'@@@    END OF REGISTRY DECLARATIONS

'@@@    ODBC DECLARATIONS
Private Declare Function SQLDataSources Lib "ODBC32.DLL" (ByVal henv&, ByVal fDirection%, ByVal szDSN$, ByVal cbDSNMax%, pcbDSN%, ByVal szDescription$, ByVal cbDescriptionMax%, pcbDescription%) As Integer

Private Declare Function SQLAllocEnv% Lib "ODBC32.DLL" (env&)
Const SQL_SUCCESS As Long = 0
Const SQL_FETCH_NEXT As Long = 1
'@@@    END OF ODBC DECLARATIONS

Private Const REG_SZ = 1    'Constant for a string variable type.
Private Const HKEY_LOCAL_MACHINE = &H80000002

Public Function CreateODBC(blnMessages As Boolean)

    'DSN Declarations
   Dim DataSourceName As String
   Dim DatabaseName As String
   Dim Description As String
   Dim DriverPath As String
   Dim DriverName As String
   Dim LastUser As String
   Dim Regional As String
   Dim Server As String

   Dim lResult As Long
   Dim hKeyHandle As Long

   'Specify the DSN parameters.
   DataSourceName = "NMC-BIS Voice"
   DatabaseName = "Voice"
   Description = "Connection for District Telecom's Database"
   'DriverPath = "SQL Server"
   LastUser = "Voice_Access"
   Server = strServerIP
   DriverName = "SQL Server"

   'Create the new DSN key.
   lResult = RegCreateKey(HKEY_LOCAL_MACHINE, "SOFTWARE\ODBC\ODBC.INI\" & _
        DataSourceName, hKeyHandle)

   'Set the values of the new DSN key.
   lResult = RegSetValueEx(hKeyHandle, "Database", 0&, REG_SZ, _
      ByVal DatabaseName, Len(DatabaseName))
   lResult = RegSetValueEx(hKeyHandle, "Description", 0&, REG_SZ, _
      ByVal Description, Len(Description))
   lResult = RegSetValueEx(hKeyHandle, "Driver", 0&, REG_SZ, _
      ByVal DriverPath, Len(DriverPath))
   lResult = RegSetValueEx(hKeyHandle, "LastUser", 0&, REG_SZ, _
      ByVal LastUser, Len(LastUser))
   lResult = RegSetValueEx(hKeyHandle, "Server", 0&, REG_SZ, _
      ByVal Server, Len(Server))

   'Close the new DSN key.
   lResult = RegCloseKey(hKeyHandle)

   'Open ODBC Data Sources key to list the new DSN in the ODBC Manager.
   'Specify the new value.
   'Close the key.
   lResult = RegCreateKey(HKEY_LOCAL_MACHINE, _
      "SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources", hKeyHandle)
   lResult = RegSetValueEx(hKeyHandle, DataSourceName, 0&, REG_SZ, _
      ByVal DriverName, Len(DriverName))
   lResult = RegCloseKey(hKeyHandle)

    If blnMessages = True Then _
    MsgBox "The ODBC Driver was created successfully", vbInformation + vbOKOnly, "ODBC Created"
   
Exit Function

errHandler:
   
    MsgBox "The ODBC Driver was not created.  Please see your system administrator.", vbCritical + vbOKOnly, "ODBC Error"

End Function

Public Function CheckODBC(strODBC As String)
   On Error Resume Next
   
   Dim i As Integer
   Dim sDSNItem As String * 1024
   Dim sDRVItem As String * 1024
   Dim sDSN As String
   Dim sDRV As String
   Dim iDSNLen As Integer
   Dim iDRVLen As Integer
   Dim lHenv As Long         'handle to the environment

   'get the DSNs
   If SQLAllocEnv(lHenv) <> -1 Then
        Do Until i <> SQL_SUCCESS
           sDSNItem = Space(1024)
           sDRVItem = Space(1024)
           i = SQLDataSources(lHenv, SQL_FETCH_NEXT, sDSNItem, 1024, iDSNLen, sDRVItem, 1024, iDRVLen)
           sDSN = VBA.Left(sDSNItem, iDSNLen)
           'check to see if ODBC exists
           If sDSN <> Space(iDSNLen) Then
                If sDSN = strODBC Then Exit Function
           End If
        Loop
   
        'ODBC Not Found, Create without any messages
        CreateODBC False
    End If

End Function
0
 
LVL 1

Author Comment

by:Y2Chris75
Comment Utility
Okay Drizzt95m, I appreciate your response, but I have to say it is well above my understanding.  I have learned a good bit from experience, but I haven't been schooled on it or anything.  Specifically the registry declarations, but some of the rest too.

...in the meantime, I have solved my problem with the code I will post below.  Since you appear to have been doing this kinda thing for a while, maybe you could help me with a slightly different issue?

*******************************START******************************
Private Sub RelinkTables_Click()
Dim OpEx as Database
Dim Path as String
Dim Cont as Container
Dim Docs(3) as Document
Dim RLnk(3) as TableDef
Dim Link(3) as String

Set OpEx = CurrentDb
Path = "ODBC;DSN=OpExDevelopment;DATABASE=OpEx"
Set Cont = OpEx.Containers!Tables

Link(0) = "05a_NVH"
Link(1) = "05b_NVH"
Link(2) = "06x_VND"
Link(3) = "07a_WOS"

If opexIsTbl(Link(0)) = True Then
DoCmd.DeleteObject acTable, Link(0)
End If

If opexIsTbl(Link(1)) = True Then
DoCmd.DeleteObject acTable, Link(1)
End If

If opexIsTbl(Link(2)) = True Then
DoCmd.DeleteObject acTable, Link(2)
End If

If opexIsTbl(Link(3)) = True Then
DoCmd.DeleteObject acTable, Link(3)
End If

Set RLnk(0) = OpEx.CreateTableDef(Link(0))
      RLnk(0).Connect = Path
      RLnk(0).SourceTableName = Link(0)

Set RLnk(1) = OpEx.CreateTableDef(Link(1))
      RLnk(1).Connect = Path
      RLnk(1).SourceTableName = Link(1)

Set RLnk(2) = OpEx.CreateTableDef(Link(2))
      RLnk(2).Connect = Path
      RLnk(2).SourceTableName = Link(2)

Set RLnk(3) = OpEx.CreateTableDef(Link(3))
      RLnk(3).Connect = Path
      RLnk(3).SourceTableName = Link(3)

OpEx.TableDefs.Append RLnk(0)
OpEx.TableDefs.Append RLnk(1)
OpEx.TableDefs.Append RLnk(2)
OpEx.TableDefs.Append RLnk(3)

Set Docs(0) = Cont.Documents(Link(0))
      Docs(0).UserName = "RestrictFull"
      Docs(0).Permissions = dbSecRetrieveData OR dbSecInsertData OR dbSecReplaceData OR dbSecDeleteData

Set Docs(1) = Cont.Documents(Link(1))
      Docs(1).UserName = "RestrictFull"
      Docs(1).Permissions = dbSecRetrieveData OR dbSecInsertData OR dbSecReplaceData OR dbSecDeleteData

Set Docs(2) = Cont.Documents(Link(2))
      Docs(2).UserName = "RestrictFull"
      Docs(2).Permissions = dbSecRetrieveData OR dbSecInsertData OR dbSecReplaceData OR dbSecDeleteData

Set Docs(3) = Cont.Documents(Link(3))
      Docs(3).UserName = "RestrictFull"
      Docs(3).Permissions = dbSecRetrieveData OR dbSecInsertData OR dbSecReplaceData OR dbSecDeleteData

MsgBox "All tables have been successfully relinked.", vbOkonly
End Sub

'''opexIsTbl (used above) is another function to see if a Table exists in the current database.
********************************END*******************************

This code is working for me, but I trimmed it up a bit to post here and show you.  I actually have 14 linked tables, instead of the 4 shown above.  With all that repetition, I would think there HAS to be a better way to structure it.  I was trying to decipher the help on "For...next" and "For....each....next", but I'm not quite getting it.

From what you see here, can you tell me a better way to write it, that would take up less space?
0
 
LVL 3

Accepted Solution

by:
Drizzt95 earned 250 total points
Comment Utility
Sorry about that, it is pretty confusing code, I don't really look at it I guess, I'm just used to using it.

There is a way to go through all of the table names and grab them out and relink them that way, but I've found it doesn't always work.  I do it pretty much the same way as you with the code a bit smaller.  It takes a little longer but it ensures it works every time.
Looking at your code it shouldn't matter what order you delete and relink them in.

Add a module and that will remove the need to use any arrays and make it look neater.

I'm not sure about the container, you may have to move it to the function as well.  I can't really see what your using it for, probably code you haven't included.

Private Sub RelinkTables_Click()
Dim OpEx as Database
Dim Path as String
Dim Cont as Container

Set OpEx = CurrentDb
Path = "ODBC;DSN=OpExDevelopment;DATABASE=OpEx"
Set Cont = OpEx.Containers!Tables

Relink "05a_NVH"
Relink "05b_NVH"
Relink "06x_VND"
Relink "07a_WOS"

MsgBox "All tables have been successfully relinked.", vbOkonly
End Sub

'add this as a function in a module
public function Relink(strTableName as String)

Dim RLnk as TableDef
Dim Docs as Document

 If opexIsTbl(strTableName= True) Then
  DoCmd.DeleteObject acTable, strTableName
 End If

 Set RLnk = OpEx.CreateTableDef(strTableName)
      RLnk.Connect = Path
      RLnk.SourceTableName = strTableName)
 OpEx.TableDefs.Append RLnk

 Set Docs = Cont.Documents(strTableName)
      Docs.UserName = "RestrictFull"
      Docs.Permissions = dbSecRetrieveData OR dbSecInsertData OR dbSecReplaceData OR dbSecDeleteData

End Function

0
 
LVL 1

Author Comment

by:Y2Chris75
Comment Utility
Drizzt95

Below is the code I was going to settle on, but yours looks like sound advice too.  Is this the type of thing that you've found "does not always work?"

The Container was necessary for setting the permissions after the links are created:
ie. Set Docs(x) = Cont.Documents(Link(x))

*******************************START******************************
Private Sub RelinkTables_Click()
Dim OpEx as Database
Dim Path as String
Dim Cont as Container
Dim Docs(3) as Document
Dim RLnk(3) as TableDef
Dim Link(3) as String

Set OpEx = CurrentDb
Path = "ODBC;DSN=OpExDevelopment;DATABASE=OpEx"
Set Cont = OpEx.Containers!Tables

Link(0) = "05a_NVH"
Link(1) = "05b_NVH"
Link(2) = "06x_VND"
Link(3) = "07a_WOS"

for x = 0 to 3
  If opexIsTbl(Link(x)) = True Then
    DoCmd.DeleteObject acTable, Link(x)
  End If

Set RLnk(x) = OpEx.CreateTableDef(Link(x))
      RLnk(x).Connect = Path
      RLnk(x).SourceTableName = Link(x)

OpEx.TableDefs.Append RLnk(x)

Set Docs(x) = Cont.Documents(Link(x))
      Docs(x).UserName = "RestrictFull"
      Docs(x).Permissions = dbSecRetrieveData OR dbSecInsertData OR dbSecReplaceData OR dbSecDeleteData

next x

MsgBox "All tables have been successfully relinked.", vbOkonly
End Sub
********************************END*******************************

Thanks for all your help,
Chris
0
 
LVL 3

Expert Comment

by:Drizzt95
Comment Utility
No, that would also work fine, I wasn't sure which way you would prefer.  The concept is similar to the function I created, it's just a matter of personal preference.

I would also recommend if you get a chance to check the code (auto ODBC Creation) out that I originally posted if you get any spare time.  It's not as confusing as it looks, and can save you a lot of time, but you still have to relink the tables, so it doesn't remove that step.

If you do get around to it, the only lines you have to understand is:

 'Specify the DSN parameters.
   DataSourceName = "NMC-BIS Voice"
   DatabaseName = "Voice"
   Description = "Connection for District Telecom's Database"
   'DriverPath = "SQL Server"
   LastUser = "Voice_Access"
   Server = strServerIP
   DriverName = "SQL Server"

Thanks
Drizzt
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now