Unanswered: modifying data across two linked databases
access 2002 sp3
windows xp home sp3
two linked databases (customers.mdb and customersdata.mdb)
(customer.mdb is the database that is opened for normal use)
this is not a database that i created. i don't know the development history. it was very likely originally created using access 8 or 9. i'm unfamiliar with access upgrade compatibility issues so i'm mentioning this in case there were ever any (i'm not seeing any problems during normal database use).
if there is any additional information i can provide that will help in helping me please don't hesitate to ask.
my primary need:
there are two linked databases that manage customer addresses and orders (low volume, ~200/year). the database has a few phone number fields that only allow 7-digit numbers. these fields need to be expanded to accommodate 10-digit phone numbers. additionally, i need to prefix all existing phone numbers with a particular area code (i imagine using some type of search and replace routine).
my initial problem (other than the fact that i'm an access newbie) was that i did not realize that there were two linked databases. so i was attempting to modify fields in tables (in customers.mdb) that were linked to another database (customersdata.mdb)--this didn't work very well :)
once i figured out that there was a second linked database (customersdata.mbd) i attempted to modify the phone number fields and data in that database. i was more successful (in that i could actually modify the phone number field properties), but not understanding the overall database design i was left wondering why my changes to the actual data (in customersdata.mdb) didn't display in the forms that were viewed when customers.mdb was invoked.
so that leads me to my first question, what do i need to learn about the database design that will help me to understand the relationship between the data that i see in customers.mdb and the data in customersdata.mdb?
my second question is how do i perform a proper search and replace on data in an access table? i think i'm mainly stuck on how to use wildcards. the example being, i have a phone number field that i've expanded to 10 characters that is populated with numbers that appear in this format "(___) 123-4567)". my initial attempt was to perform a search and replace on just the area code portion by searching for "(???)" and replacing with "(111)". access complained about not being able to find the given string. so then i attempted to search for "(???) ???-????" and replace with "(111) ???-????". this worked better in that access could find the string, but i was hoping that by specifying wildcards in the replace string that access would leave the existing 7-digit data untouched. no-go, access replaced the 7-digit data with question marks.
i then attempted to copy the data to an external editor, perform the search and replace there, and paste the corrected data back into the table. access didn't appear to like me pasting data back into the table (it issued some type of "primary key" complaint that i did not record). i may have gotten further with the pasting of the data back into the table but i suspended my investigation in order to post to this forum.
i've also seen a seemingly odd error message as i've poked around behind the scenes. if anyone could shed any light on this i'd be very grateful.
when switching from the Design to the Datasheet view for any of the tables in customers.mdb i always see this message (i also see this message if i'm in the Datasheet view for any table in customers.mdb and switch to customersdata.mdb):
Microsoft Visual Basic
Run-time error '2194':
You can't set the PictureData property in Datasheet view.