I need specific code examples for copying ODBC linked Visual Foxpro
table fields into an existing Access database.
The examples need to include methods to convert data types (formats?) - example:
Foxpro date field may be mm/dd/yyyy and the destination field in the
access database may be yyyy/mm/dd.
Alternately, a conversion program of some kind allowing the above type
example to be reached would be fine as well.
I have successfully ODBC linked the Foxpro data, no information
required in that regard. |
Clarification of Question by
ufi911-ga
on
22 Dec 2004 06:14 PST
After thinking it over - I worded the question more complicated than
it needed to be.
Forget about the OBDC linked fields - All I really need is examples to
copy one field in a table to another field in a table, with the
ability to do conversion like I stated before.
The reason for this clarification is I already have the data linked,
so I believe Access will treat the linked fields like normal Access
fields.
|
Request for Question Clarification by
mathtalk-ga
on
22 Dec 2004 06:24 PST
Hi, ufi911-ga:
The syntax for an UPDATE statement involving updating one table from
another in Access is a bit strange. I've posted an example of doing
this on another Question, here:
http://answers.google.com/answers/threadview?id=445635
It sort of says, UPDATE the join of two tables. [It would be really
strange if Access let's you update both "sides" of the join, and I
haven't tried it.]
If you are doing the query with linked tables in Access, I'd expect it
to handle the date conversion from FoxPro to Access for you. If you'd
like more specific syntax, why not post the table descriptions, or at
least enough of the fields to be able to detail what "joins" the two
and what needs to be updated with what?
regards, mathtalk-ga
|
Clarification of Question by
ufi911-ga
on
22 Dec 2004 08:47 PST
Mathtalk,
Here is a portion of the structure of my source data:
Structure for table: V:\DATA\COMPANY.DBF
Number of data records: 554
Date of last update: 12/20/2004
Memo file block size: 64
Code Page: 1252
Field Field Name Type Width Dec Index Collate Nulls
1 COMP_ID Character 6 Asc Machine No
2 BETA Logical 1 No
3 CADDRESS_1 Character 42 No
4 CADDRESS_2 Character 42 No
5 CADDRESS_3 Character 42 No
6 CADDRESS_4 Character 42 No
7 CERTS_H Numeric 1 No
8 CERTS_M Numeric 1 No
9 CERTS_OP Numeric 1 No
10 CFAX Character 20 No
11 CITY Character 30 No
12 CNAME Character 58 Asc Machine No
For example sake, I need to copy the data from the CNAME field in this
file into an existing table in Access called "Customer Master" the
field name is "Company Name".
I have literally dozens if not 100's of fields that need this type of updating.
In a perfect world I would like to code small modules that I can pass
arguments to so I can recycle the code over and over again. In an
even more perfect world, the arguments would be stored in a table, and
the code would just loop through the table until finished. This may
be beyond my abilities, but I won't know if I don't try.
Thanks,
-John
|
Request for Question Clarification by
mathtalk-ga
on
22 Dec 2004 11:01 PST
What external link name do you have for the FoxPro table in your
Access database, and what fields do you compare to "join" the external
table to your internal table called "Company Master"?
regards, mathtalk-ga
|
Clarification of Question by
ufi911-ga
on
23 Dec 2004 09:08 PST
The link name is 'company' (the name came from company.dbf as shown at
the top of the structure listing)
And I hope I understand the 2nd question - 'Company Master' (with a
space) is the destination table name in Access.
Thank you for your help.
-John
|
Request for Question Clarification by
mathtalk-ga
on
23 Dec 2004 09:55 PST
The second part of my question has to do with figuring out which row
in "company" goes with which row in "Company Master".
For example, it might be that in "company" the field COMP_ID is a
unique identifier, one row per value of that column, and that you have
another column of the exact same fieldname COMP_ID in "Company
Master".
The "join" between the two tables could then be as simple as this condition:
WHERE [company].COMP_ID = [Company Master].COMP_ID
We definitely can't use CNAME as part of the join condition since the
goal is to UPDATE rows in "Company Master" with the CNAME taken from
"company".
But you've not told me anything about the columns in "Company Master",
so I feel it would be expeditious to have your description of that
table, together with an explanation of how to "match" the rows between
the two tables.
regards, mathtalk-ga
|
Clarification of Question by
ufi911-ga
on
23 Dec 2004 16:22 PST
Mathtalk - Thanks for being patient with me, I'm slow but steady :)
Foxpro file called Company.dbf (source file):
comp_id cname caddress1 caddress2
Access table called Customer Master (destination table):
Customer Number Customer Name Address Line 1 Address Line 2
comp_id and Customer Number would be the unique field between the two.
A value would never repeat for comp_id. Does that answer your
question?
For what it's worth, I've certainly gotten my $5 out of you already, I
would be happy to close this question and pay you and start on
another? I don't want to break any rules, I just wish I would have
made the question worth more than $5.
Thanks,
-John
|
Request for Question Clarification by
mathtalk-ga
on
24 Dec 2004 09:41 PST
Hi, John:
While you can change the list price offered anytime before it's
answered (and unlocked), I'm happy to get you squared away on the
immediate Question.
It is reasonable to create an UPDATE "query" in Access that does what
you want for these two particular tables. I'm concerned about not the
immediate case, but what you refer to as having "dozens if not 100's"
of fields that need to be updated in a similar way. I wouldn't know
how to easily automate such a task for you; it would require knowing
(for each pair of tables) the sort of information we've discussed here
about these two tables: their names, their columns' names, what
"joins" the tables, and what needs to be updated from one table to the
other.
Maybe there is a lot of similarity between the different situations,
but I'd be guessing at ways to try and automate the process without
knowing much more.
regards, mathtalk-ga
|
