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