Solved

Change table structure with a split database.

Posted on 2004-03-29
8
303 Views
Last Modified: 2008-03-17
I have been given the task of updating a distributed Access solution.  There is a need to add new fields to the database which has been split.

As I can not seem to use SQL to do this what can I do to make changes?
0
Comment
Question by:Matt-the-HAT
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 65

Expert Comment

by:rockiroads
ID: 10703991
Can you not go to the database in question then modify the table structure itself?

within the database window, click on Tables, then open up the table in design view

regarding the front-end, I assume they are linked tables, you can always delete and relink them either manually or by VBA code

If you require VBA code to do the relinking, I can certainly provide you with some
0
 
LVL 4

Expert Comment

by:goliak
ID: 10704564
You should use SQL!!! To create a new table use CREATE TABLE sentence. To add fields to an existing table use ALTER TABLE sentence (look Jet SQL help for details).

And this way you can create a connection to a distant DB:

Dim db As DAO.Database
Set db = Workspaces(0).OpenDatabase("\\Tyan\Common\Velin\CompCom\CompComS_be.mdb", , False)
db.execute "Your sql sentence which adds new fields to the table"

But to change a table you must enter the DB monopolly.
Regards, Goliak.
0
 
LVL 2

Author Comment

by:Matt-the-HAT
ID: 10722858
rockiroads: unfortunatly I do not have access to the backend

But you have a point about useing VBA to relink after the update... I shall double the points on offer to cover the inclusion of such code.

goliak: your answer worked like a dream!   an easy 125 points will be heading your way soon.
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 65

Expert Comment

by:rockiroads
ID: 10722901
I used this code to change the links on a frontend database that has table links to the backend

'Pass in path of DB, password if any, TRUE to create or FALSE to delete (note you got to delete existing before recreating)

Public Function Install_RefreshLinks(ByVal sDB As String, ByVal sPassword As String, ByVal bCreateLinks As Boolean) As Boolean
   
    Dim dbData As Database
    Dim tdData As TableDef
    Dim sTmp As String
    Dim i As Integer
    Dim bLoop As Boolean
    Dim iAbortLoop As Integer
    Dim bDone As Boolean
    Dim iCnt As Integer
 
 
    On Error GoTo ins_err
   
    Install_RefreshLinks = False
   
'Using refreshlink keeps failing with installable isam error,
'To avoid this on other client machines, Im cheating by deleting first

    Err.clear
    iAbortLoop = 0
   
    If bCreateLinks = False Then
        Set dbData = CurrentDb
        bLoop = True
        iCnt = 0
        While bLoop = True
            bDone = False
            For Each tdData In dbData.TableDefs
                If tdData.Attributes = dbAttachedTable Then
                    If tdData.Name <> "RPT_Temp" Then
                        'Sleep (1000)
                        dbData.TableDefs.Delete tdData.Name
                        iCnt = iCnt + 1
                    End If
                    bDone = True
                End If
            Next tdData
            iAbortLoop = iAbortLoop + 1
            If bDone = False Or iAbortLoop = 500 Then
                bLoop = False
                MsgBox iCnt & " linked tables were removed.", vbInformation, APP_TITLE
            End If
        Wend
        'Now recreate links
        'If Err.Number = 0 Then bCreateLinks = True
    End If
 
    If bCreateLinks = True Then
        iCnt = 0
        If sPassword <> "" Then
            Set dbData = DBEngine.Workspaces(0).openDatabase(sDB, False, False, ";PWD=" & sPassword)
        Else
            Set dbData = DBEngine.Workspaces(0).openDatabase(sDB)
        End If
   
        For Each tdData In dbData.TableDefs
            If tdData.Attributes = 0 Then
                If tdData.Name <> "RPT_Temp" And tdData.Name <> "MainMenu" Then
                    'Cater for temp tables used by Dev but not for release
                    If Left$(tdData.Name, 3) <> "xx_" Then
                        DoCmd.TransferDatabase acLink, "Microsoft Access", sDB, acTable, tdData.Name, tdData.Name, False
                        iCnt = iCnt + 1
                    End If
                End If
            End If
        Next
   
        MsgBox iCnt & " linked tables were created.", vbInformation, APP_TITLE
    End If

    Install_RefreshLinks = True
    GoTo ins_ok
   
ins_err:
    MsgBox "Error " & Err.Number & " trapped. " & vbCrLf & Err.Description, vbCritical, APP_TITLE

ins_ok:
    Set dbData = Nothing
End Function
0
 
LVL 2

Author Comment

by:Matt-the-HAT
ID: 10916847
in the end I found this did the job

Public Sub something()
    Dim db As DAO.Database
    Set db = Workspaces(0).OpenDatabase("C:\sb-Data\sb\sb_be.mdb", , False)
    db.Execute "ALTER TABLE [XS_FE] drop COLUMN FE_MarkUp SINGLE"
End Sub
0
 

Accepted Solution

by:
modulo earned 0 total points
ID: 10962594
Closed, 250 points refunded.

modulo
Community Support Moderator
Experts Exchange
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

617 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