Re: get external data odbc limitation?
- From: "Aaron Kempf" <akempf@xxxxxxxxxx>
- Date: Wed, 2 May 2007 13:14:22 -0700
if you want to put information into 'another database' u should create a 2nd
SQL database not a MDB
Select * from database1.dbo.MyTable can refer to Select * from
database2.dbo.MyTable without any connection string crap
Congrats on moving to ADP!
ADP rocks!
"college researcher" <collegeresearcher@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message news:DC6088F2-BC20-49ED-9F20-F61B70A37537@xxxxxxxxxxxxxxxx
Since the last post has anyone found another way to get around the AccessAccess
ODBC table linking limitation '69 Camero/Gunny cites -- i.e., that an
Jet (.mdb) database will show only 64K of characters in returning a listStudent/HR
(table names in this case)?
I'm a college institutional researcher and also must contend with
PeopleSoft's 50,000+ Finance tables (on SQL Server), plus those in
8.9 (residing by PeopleSoft design on a separate server). While my DBA(all
seem to be stretched thin these days) has provided the solution Gunnycites
(i.e., "create a role that has permissions limited to only the tablesneeded,
create a new user and assign it to that role"), for a researcher it's bestfraction
for a number of reasons to be able to see all tables. While only a
of PeopleSoft's tables may be needed for research purposes, PeopleSoftsee
doesn't provide a roadmap, table descriptions, etc., and one must do their
own mapping and stay on top of tables being used and added. If one can't
the table names and test/sample them as needed, it's extremely difficultto
give one's DBA "the" limited list of tables needed.and
In terms of one alternative that has been suboptimal so far, I have been
able to link to all tables in PeopleSoft Student 8.9 via use of Access
Project (.adp), but haven't figured out a better way than export/import to
get records/tables needed into my Access Jet database. My Jet databases
incorporate tables from other data sources as well (via ODBC or import),
it's relatively easy and fast to transform fields in tables from differentcrosswalks,
sources as needed (e.g., convert numbers stored as text, perform
etc.), and further link those tables to selected records/tables from(copyright
PeopleSoft (whether linked via ODBC or "downloaded" via an Access "make
table" query). Further, I use an add-in called "CSD Tools 2000"
2007, Conrad Systems Development) that assists greatly in mapping tasks --databases
i.e., it creates a list of an Access databases tables (including linked
PeopleSoft and other tables), their fields, properties, record count, etc.
This can then be imported as part of an overall "data dictionary" (which
PeopleSoft lacks). I haven't gotten CDS Tools to work with Access Project
(or found a substitute).
In brief, there are other advantages (including compatibility with other
college office software in use) & time savings in using Access Jet
for college institutional research, so I/others would greatly appreciateany
suggestions or solutions (direction, tools, ad-ins, whatever) for gettingat
Access Jet ODBC to link or show all PeopleSoft tables on SQL Server. I'm
the power user level, and would tap my DBA regarding technical suggestionsor
material anyone may with to cite.
--
college researcher
.
- References:
- Re: get external data odbc limitation?
- From: college researcher
- Re: get external data odbc limitation?
- Prev by Date: Re: get external data odbc limitation?
- Next by Date: Re: get external data odbc limitation?
- Previous by thread: Re: get external data odbc limitation?
- Next by thread: Re: get external data odbc limitation?
- Index(es):
Relevant Pages
|
