Import Teradata SQL data into Access database table

[KMEscherich]

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.

[Zwoker]

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.

[KMEscherich]

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.

Private Sub LBL_RECEIPTS_Click()
On Error GoTo Err_LBL_RECEIPTS_Click
 
  DoCmd.TransferDatabase transfertype:=acImport, _
  databasetype:="Microsoft Access", _
  databasename:="C:\KATHY\TAX_DEDUCTIONS.mdb", _
  objecttype:=acTable, Source:="T_RECEIPTS", _
  destination:="T_RECEIPTS"

Dim SQL_Text As String
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 "
DoCmd.RunSQL (SQL_Text)

Exit_LBL_RECEIPTS_Click:
    Exit Sub

Err_LBL_RECEIPTS_Click:
    MsgBox Err.Description
    Resume Exit_LBL_RECEIPTS_Click

End Sub

[Zwoker]

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:

SQLString = "SELECT RECEIPT_ID, RECEIPT_NUMBER FROM PAT_ENC WHERE dept = 'GMPNM';"
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.

[NeoPa]

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.