?
Solved

Update queries...I'm stuck!

Posted on 2005-05-06
5
Medium Priority
?
227 Views
Last Modified: 2012-08-13
Here's the scenario...

I've created an Outlook form that includes VBa to fire the results into an Access table (outlookdata).  To get this to work I am using a Linked table from a database that is stored on the network, but the data from the Outlook form is fired into a database on my machine, which includes the linked table from the network.

In a test environment this works fine, when i activate the Macro containing the VBa, it fires into the database on my machine, which in-turn populates the table on the Network...the problem is that I am taking over a database designed by someone else, however the field names are not all the same:

The database on my machine has the following table fields:

SPNumber, FullName, Summary, Impact.

The database I'm taking over (on the network) has the following that contains the same information, but named differently:

SPnumber, Name, Proposal, Decision

Would it be OK to rename the fields on the network database to match the DB on my machine?  Or can I do some kind of query that would "convert" the details in say, 'FullName' to 'Name'?



0
Comment
Question by:magikroom
  • 3
  • 2
5 Comments
 
LVL 15

Expert Comment

by:will_scarlet7
ID: 13943509
How are you passing the data to the table on your machine? Are you using ADO?
I would recommend caution on renaming the fields in the table on the server in case there is anything else that uses that table. It would see easier to re-wrok your current project to use the new names then to try to figure out all the things that depend on the old names and re-work them.
0
 

Author Comment

by:magikroom
ID: 13943531
I'm sending it from the Outlook Form to the table on my machine via a bit of code I tracked down and tweaked to suit.  The fields (user defined) on the outlook form are identical to the field names in the table on my machine, which is linked from the same table stored on the network...it all works fine, it's just that I was looking for a way to translate one table to another...not the actual data stored in each field:

Public Sub SendAndSaveMessageToAccess()

On Error GoTo ErrorHandler
   
   Set ins = Application.ActiveInspector
   Set itm = ins.CurrentItem
   
   If itm.Class <> olMail Then
      MsgBox "The active Inspector is not a mail message; exiting"
      GoTo ErrorHandlerExit
      'Could add more error-trapping to determine if the mail message uses a
      'specific custom form, or has specific data in one or more fields
   
   Else
      Set msg = itm
     
      'Pick up path to Access database directory from Access SysCmd function
      Set appAccess = CreateObject("Access.Application")
      strAccessPath = appAccess.SysCmd(acSysCmdAccessDir)
      strAccessPath = strAccessPath & "Outlook Data\"
      Debug.Print "Access database path: " & strAccessPath
   
      'Check that there is an Outlook Contacts folder under the Access
      'database folder, and exit if not found
      Set fso = CreateObject("Scripting.FileSystemObject")
      Set fld = fso.GetFolder(strAccessPath)
      Set dbe = CreateObject("DAO.DBEngine.36")
      strDBName = "New Proposals.mdb"
      strDBNameAndPath = strAccessPath & strDBName
      Debug.Print "Database name: " & strDBNameAndPath
   
      'Test for existence of database
      Set fil = fso.GetFile(strDBNameAndPath)
      Set wks = dbe.Workspaces(0)
      Set dbs = wks.OpenDatabase(strDBNameAndPath)
   
      'Open Access table containing mail message data
      Set rst = dbs.OpenRecordset("outlookdata")
      rst.AddNew
               
      'Save data from custom fields to Access table (if they exist)
      Set ups = msg.UserProperties
      Set prp = ups.Find("BenefitComments")
      If TypeName(prp) <> "Nothing" Then
         If prp.Value <> "" Then
            rst!BenefitComments = ups("BenefitComments").Value
         End If
      End If
     
      Set prp = ups.Find("FilterGroupComments")
      If TypeName(prp) <> "Nothing" Then
         If prp.Value <> "" Then
            rst!FilterGroupComments = ups("FilterGroupComments").Value
         End If
      End If
     
      Set prp = ups.Find("FulNameBox")
      If TypeName(prp) <> "Nothing" Then
         If prp.Value <> "" Then
            rst!FulNameBox = ups("FulNameBox").Value
         End If
      End If
     
      Set prp = ups.Find("GradeBox")
      If TypeName(prp) <> "Nothing" Then
         If prp.Value <> "" Then
            rst!GradeBox = ups("GradeBox").Value
         End If
      End If
     
      Set prp = ups.Find("ImpactComments")
      If TypeName(prp) <> "Nothing" Then
         If prp.Value <> "" Then
            rst!ImpactComments = ups("ImpactComments").Value
         End If
      End If
     
     Set prp = ups.Find("IncludeDetails")
      If TypeName(prp) <> "Nothing" Then
         If prp.Value <> "" Then
            rst!IncludeDetails = ups("IncludeDetails").Value
         End If
      End If
     
      Set prp = ups.Find("LiaisonRepBox")
      If TypeName(prp) <> "Nothing" Then
         If prp.Value <> "" Then
            rst!LiaisonRepBox = ups("LiaisonRepBox").Value
         End If
      End If
     
      Set prp = ups.Find("LocationBox")
      If TypeName(prp) <> "Nothing" Then
         If prp.Value <> "" Then
            rst!LocationBox = ups("LocationBox").Value
         End If
      End If
     
      Set prp = ups.Find("RoomBox")
      If TypeName(prp) <> "Nothing" Then
         If prp.Value <> "" Then
            rst!RoomBox = ups("RoomBox").Value
         End If
      End If
     
      Set prp = ups.Find("SecretariatQA")
      If TypeName(prp) <> "Nothing" Then
         If prp.Value <> "" Then
            rst!SecretariatQA = ups("SecretariatQA").Value
         End If
      End If
     
      Set prp = ups.Find("SectionBox")
      If TypeName(prp) <> "Nothing" Then
         If prp.Value <> "" Then
            rst!SectionBox = ups("SectionBox").Value
         End If
      End If
     
      Set prp = ups.Find("StaffNumberBox")
      If TypeName(prp) <> "Nothing" Then
         If prp.Value <> "" Then
            rst!StaffNumberBox = ups("StaffNumberBox").Value
         End If
      End If
     
      Set prp = ups.Find("SummaryComments")
      If TypeName(prp) <> "Nothing" Then
         If prp.Value <> "" Then
            rst!SummaryComments = ups("SummaryComments").Value
         End If
      End If
               
      Set prp = ups.Find("TelephoneBox")
      If TypeName(prp) <> "Nothing" Then
         If prp.Value <> "" Then
            rst!TelephoneBox = ups("TelephoneBox").Value
         End If
      End If
     
      Set prp = ups.Find("WorkaroundComments")
      If TypeName(prp) <> "Nothing" Then
         If prp.Value <> "" Then
            rst!WorkaroundComments = ups("WorkaroundComments").Value
         End If
      End If
     
      Set prp = ups.Find("WorkaroundNo")
      If TypeName(prp) <> "Nothing" Then
         If prp.Value <> "" Then
            rst!WorkaroundNo = ups("WorkaroundNo").Value
         End If
      End If
     
      Set prp = ups.Find("WorkaroundYes")
      If TypeName(prp) <> "Nothing" Then
         If prp.Value <> "" Then
            rst!WorkaroundYes = ups("WorkaroundYes").Value
         End If
      End If
     
      Set prp = ups.Find("SPNumber")
      If TypeName(prp) <> "Nothing" Then
         If prp.Value <> "" Then
            rst!SPNumber = ups("SPNumber").Value
         End If
      End If
       
      rst.Update
      rst.Close
      dbs.Close
      Set wks = Nothing
      Set dbe = Nothing
      Set appAccess = Nothing
     
      MsgBox msg.Subject & " data has now been exported to the Database"
     
   
   End If
   
ErrorHandlerExit:
   Exit Sub

ErrorHandler:
   If Err.Number = 76 Then
      Set fld = fso.CreateFolder(strAccessPath)
      MsgBox strAccessPath & _
         " folder created; please copy the appropriate Access database to it and try again"
      GoTo ErrorHandlerExit
   Else
      MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
      Resume ErrorHandlerExit
   End If

End Sub
0
 
LVL 15

Accepted Solution

by:
will_scarlet7 earned 750 total points
ID: 13943710
I would think the proper thing would be to go through the code and replace the field names to reflect the names in the table. For example this:

           rst!FulNameBox = ups("FulNameBox").Value

would become:

           rst!NameBox = ups("FulNameBox").Value

However it might be easier to do as you proposed and create a query that uses an alias to refer to the fields in question. Something like:

    SELECT SPnumber, Name AS FullName, Proposal AS Summary, Decision AS Impact;
0
 
LVL 15

Expert Comment

by:will_scarlet7
ID: 13943890
I hope it was a help.
God bless!

Sam
0
 

Author Comment

by:magikroom
ID: 13943976
Yeah it was, I tried to alter rst!NameBox = ups("FulNameBox").Value, but altered "("FulNameBox")." as well and it didn't work...now sorted...cheers

Jamie
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
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…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

840 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