How to use Teradata as backend in Access via ODBC Connection

Posted on 2008-02-04
Medium Priority
Last Modified: 2011-04-14

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!
Question by:c9k9h
  • 2
  • 2
LVL 10

Expert Comment

ID: 20817187
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

Author Comment

ID: 20817240
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?
LVL 10

Accepted Solution

Smart_Man earned 2000 total points
ID: 20818665
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.


Author Comment

ID: 20826695
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!

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
In this article, we will show how to detach and attach a database and then show how to repair a corrupt database and attach it, If it has some errors. We will show how to detach and attach using SSMS or using T-SQL sentences.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

601 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question