473,320 Members | 1,965 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Import Teradata SQL data into Access database table

Version of Access = 2003

Hi there, is there a way to capture data using TERADATA SQL Assistant and import a portion of this database table into a Microsoft Access database table???

I currently have Teradata SQL Assistant and write SQL to retrieve data from clarity tables. I would like to have a button on a Microsoft Access form that would retrieve data directly from Clarity tables and import into a specific Microsoft Access database table.

Am not sure if this is possible. If this is possible, can someone please give me an example of code that would be used in order to accomplish this???

Thank you in advance for your assistance.
Jun 2 '08 #1
4 13958
Zwoker
66
Hi,

If you wanted to code something using VBA in MS Access that connected to the Teradata database, via ODBC, and retreived whatever data you wanted, then that should be reasonably simple.

If you want to try this then let me know and I can give you an example of a connection string and code that should work, assuming you already have the ODBC stuff setup for your Teradata database on your PC.


Regards,
Zwoker.
Jun 3 '08 #2
I am not sure if this is what you are talking abuot, but when I am in Access I can do the following:

FILE / Get External Data / Import
Files of Type = ODBC
Machine Data Source tab = CLR_SC_ODBC
DBC Name or Address = SCAL-Clarity.kp.org
User Name = 1234
Password = ABCD

After doing this, it shows all the clarity tables.

I would like to be able to specify in a SQL query that I want to retrieve all records from the PAT_ENC table where CONTACT_DATE between Jan 1, 2008 and Jan 31, 2008 and lets say a dept = 'GMPNM'. Then have this data populate a table with the same fields in my Access database.

THANK YOU VERY MUCH for your assistance.



PS: I thought I would try to run the following code to transfer data from one Access database to another just to see how it would work. It would not put the data into the actual table, it would create a new table. I would have to go into the database and delete the newly created table after the data had been populated into the main table.

Not sure what I am doing wrong.
Expand|Select|Wrap|Line Numbers
  1. Private Sub LBL_RECEIPTS_Click()
  2. On Error GoTo Err_LBL_RECEIPTS_Click
  3.  
  4.   DoCmd.TransferDatabase transfertype:=acImport, _
  5.   databasetype:="Microsoft Access", _
  6.   databasename:="C:\KATHY\TAX_DEDUCTIONS.mdb", _
  7.   objecttype:=acTable, Source:="T_RECEIPTS", _
  8.   destination:="T_RECEIPTS"
  9.  
  10. Dim SQL_Text As String
  11. SQL_Text = "INSERT INTO T_RECEIPTS ( RECEIPT_ID, RECEIPT_NUMBER, [DATE], AMOUNT, DESCRIPTION, ACCOUNT_ID, VENDOR_ID, MILEAGE, NAME_ID ) SELECT T_RECEIPTS1.RECEIPT_ID, T_RECEIPTS1.RECEIPT_NUMBER,T_RECEIPTS1.DATE, T_RECEIPTS1.AMOUNT, T_RECEIPTS1.DESCRIPTION,T_RECEIPTS1.ACCOUNT_ID, T_RECEIPTS1.VENDOR_ID, T_RECEIPTS1.MILEAGE,T_RECEIPTS1.NAME_ID FROM T_RECEIPTS1 "
  12. DoCmd.RunSQL (SQL_Text)
  13.  
  14. Exit_LBL_RECEIPTS_Click:
  15.     Exit Sub
  16.  
  17. Err_LBL_RECEIPTS_Click:
  18.     MsgBox Err.Description
  19.     Resume Exit_LBL_RECEIPTS_Click
  20.  
  21. End Sub
Jun 3 '08 #3
Zwoker
66
Hi,

It sounds like you have everything in place already to do what you want.

If you have set up the tables from your SQL repository as external linked tables in MS Access, then you should be able to open them directly in VBA code as if they are local tables.

Alternatively you can reference them in SQL code as if they are local tables. You seem to have the basics covered already in your example SQL code above, except that it seems to be more elaborate than required (from my limited understanding).

You can just build your SQL string to select the fields you want, from the SQL table (as if it is a local table) and include whatever WHERE statements you need for your selection criteria.

A simple example of building a resultset from your SQL data would be:

Expand|Select|Wrap|Line Numbers
  1. SQLString = "SELECT RECEIPT_ID, RECEIPT_NUMBER FROM PAT_ENC WHERE dept = 'GMPNM';"
  2. InitialRst.Open SQLString
Or you can use your DoCmd.RunSQL example to put the result directly into a new table.

This assumes that you have declared your external SQL table PAT_ENC as a linked table in MS Access.

If this isn't helping then let me know. We may not be talking about the same things.


Regards,
Zwoker.
Jun 3 '08 #4
NeoPa
32,556 Expert Mod 16PB
KMEscherich, As a full member now, you should know that we expect your code to be posted in [code] tags (See How to Ask a Question).
This makes it easier for our Experts to read and understand it. Failing to do so creates extra work for the moderators, thus wasting resources, otherwise available to answer the members' questions.
Please use the tags in future.

ADMIN.
Jun 6 '08 #5

Sign in to post your reply or Sign up for a free account.

Similar topics

3
by: Doug Baroter | last post by:
Hi, One of my clients has the following situation. They use Access DB for data update etc. some business functions while they also want to view the Access data quickly and more efficiently in...
3
by: Jon Ole Hedne | last post by:
My Access 2002-application need to work with tables from both Oracle and Access. To solve this, I want to run some querys on three views in Oracle and import the results into temporary...
1
by: Dan | last post by:
Could someone please help me with auto importing a series of data files into an Access table. I tried to follow code given below in a previous messagebut i'm getting error messages. Here's my...
6
by: Noozer | last post by:
We've created a new database and need to import the data from our old database. Both are MS Access databases and each have multiple tables. The table structure of each table is completely different...
7
by: Randy | last post by:
Folks: We have a web-based app that's _really_ slowing down because multiple clients are writing their own private data into a single, central database. I guess the previous programmer did...
3
by: nolanmadson | last post by:
I'm creating a front-end for some Teradata user maintenance tables in MS Access. I've started having problems in occasionally not being able to insert or update records in these tables. I've been...
4
by: Earl Anderson | last post by:
I guess I missed the boat on the logic for this one. Immediately upon hitting "Import" in an attempt to import an Excel file containing 7 columns of 'txt' formatted data into AXP, I got a "Type...
9
by: a | last post by:
Dear friends I want import data from CSV file to mdb file How can I do that in vb.net?
6
by: provor | last post by:
Hello, I have the following code that I am using when a user presses a button to import an excel file into a table. The code is hard coded to point to the correct table. This works great for this...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.