The DTS wizard in SQL would allow you to connect to external data sources, such as Access, or an Excel table (there are a list of available data sources you can connect to), allow you to map all the columns of the chosen data source, to existing tables in SQL, or the option or creating a new table in SQL (the create table statement is auto generated in SQL but can be modified/tweaked as per your requirement before execution).
Its pretty intuitive, the first time I did it I did so without reading the manuals or any documentation, just followed the prompts.. so its pretty easy to figure out... After all the data is transfered or copied, conversion might be required for date time fields if it is not in the integer (epoch time) format. Also make sure that you have one column that would act as the Entry-ID which would have to be unique and less than 15 characters.. If you do not have that, create a serial number column in Access (or excel as the case may be) and use that as the Entry-ID column.. Joe D'Souza ----- Original Message ---- From: "Reiser, John J" <[EMAIL PROTECTED]> To: [email protected] Sent: Tuesday, January 9, 2007 3:19:34 PM Subject: Re: Access DB to Remedy ** Rick, Is the DTS anything like using the Upsizing wizard in Access? You can keep your old data but I think the Local table will not get updated once the Upsizing is completed. Your application gets connected to the remote (to ms access) db. Or does DTS give you a view into the MSC Access db file? John J. Reiser Software Development Analyst Remedy Administrator/Developer Lockheed Martin - MS2 The star that burns twice as bright burns half as long. Pay close attention and be illuminated by its brilliance. - paraphrased by me From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Rick Cook Sent: Tuesday, January 09, 2007 2:24 PM To: [email protected] Subject: Re: Access DB to Remedy ** Well, I would suggest that you leave your original table alone, and just make a copy of it in SQL, which is what the DTS tool does. Then play with the copy. I'm not sure what import options exist for Access, but I would imagine that .csv or .xml would be among them. Rick From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Christopher Gillman Sent: Tuesday, January 09, 2007 11:20 AM To: [email protected] Subject: Re: Access DB to Remedy ** Rick and L.J. Thanks for the help. It’s not a critical thing, more of a “it’d be nice to do…” but I think I’ll try later tonight to convert it to a SQL DB, and then using a view form. My only question would be… if I needed to convert data back to access, could I just export in .csv format and then import it? From: Action Request System discussion list(ARSList) [mailto: [email protected] ] On Behalf Of Rick Cook Sent: Tuesday, January 09, 2007 12:52 PM To: [email protected] Subject: Re: Access DB to Remedy Or, you can use SQL DTS (Data Transformation Service) to convert the file into a SQL DB, (there's already an Access connector), from which you could access the existing data via a View form. You could also import it into an existing DB table, but I'd be VERY careful about doing so into an existing Remedy form. If you create a new form in Remedy with the proper fields, and then import the data, as L.J. said, that will keep Remedy's metatables much happier. As usual, there are multiple ways to skin this cat, and you have to decide which one works best for you. Rick From: Action Request System discussion list(ARSList) [mailto: [email protected] ] On Behalf Of L. J. Head Sent: Tuesday, January 09, 2007 10:46 AM To: [email protected] Subject: Re: Access DB to Remedy ** Are you looking to just get the Data into Remedy? If so you will need to create the tables in remedy manually...and then you can export from Access into CSV and then simply import the data From: Action Request System discussion list(ARSList) [mailto: [email protected] ] On Behalf Of Christopher Gillman Sent: Tuesday, January 09, 2007 11:36 AM To: [email protected] Subject: Access DB to Remedy ** Listers, This has probably been covered at one point or another, but I’m wondering if there’s an easy way to take an Access database and either 1) import it into Remedy 2) Use some sort of Plugin for Remedy 3) Perhaps an ODBC connection to it. The access DB is located physically on the same system as Remedy’s DB. Just looking for ways to do this without having to re-create the whole thing in Remedy. Help is appreciated! J Thanks __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where the Answers Are"
