How to use Teradata as backend in Access via ODBC Connection


After experiencing the common problems with access and multiple users I have decided to look for a new solution. What I am trying to do is create a Teradata back end for my existing Access DB. I've gotten as far as creating a table modeled from the back end .mdb table it currently uses.  Is there a relatively simple way to set up an odbc connection that will allow multiple users to use the front-end of the database to add/delete/manipulate the data on the Teradata server? I am a bit familiar with setting up a SQL pass through that simply appends data from the server, but I want it to be live.

Thanks for any help/advice you can give!
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

if you have exported your tables from access to sql server. you can create an adp (interface) using the access to connect to your data on sql server..

waiting for your reply
c9k9hAuthor Commented:
well I have not exported any tables, rather I created them via TeraData Admin. Would it work the same way as if i were to be using MS SQL server? Also, how much will it take to transfer all of my forms, queries etc... to the adp from the existing front-end mdb?
as far as I understand you already have the msaccess database ready and with the tables and the forms and everything.
and you have re-created he tables again on the teradata.

have you managed to get those table data copied or moved from access to teradata yet ?

about your access forms , do they have any vba code , macros , .... or just created using the wizard ?

usually you can use all the built-in advantages for the access and just change the connect string or the db source using the suitable ODBC in your windows OS control panel.

about the queries , SQL is a standard language , well the ANSI SQL. anyways , vendors , microsoft , oracle , ...and others have little variations .. this is called vendor-sql. or whatever each vendor names they have for their extra advantages there.

i recommend you to try the ms access help. there is a good article there about the difference between the ms access jet sql and the ansi sql. to know if your quires are standard and ready to go or they have to have some modifications.

waiting for your reply , for more details to be provided by you and more help i hope i can post here for you.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
c9k9hAuthor Commented:
Thank you!

Yes there is LOTS of VBA code. I have established a connection via odbc to the teradata back-end. With a few code modifications, everything seems to be working decently. My only question, however, is: how to  create an auto-number substitute so to speak. Teradata does not offer any autonum. I tried to do something like OnLoad me.RequestID = RequestID + 1 and the likes, but no go...

Any suggestions for this?

Also, I have not exported the access tables/data because I cant due to the fact that Teradata does not recognize the "memo" data type and throws me an error. Next step is to try OLELOAD.... Let me know what you think and thank you for your reply!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.