Hi every, I'm new to the forum and trying to feel my way around vba in Access.
I have a Access db with one table containing no records. I'm trying to query a Teradata db and populate the Access table with the results. I believe everything is working up until me not knowing how to actually get the result into my Access table.
Public Function EDW_conn()
Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Dim cmdSQLData As ADODB.Command
Set cmdSQLData = New ADODB.Command
Dim db As Database
cn.Open "Data Source=EDW; Database=claim_prod_view_db; Persist Security Info=True; User ID=userid; Password=password; Session Mode=ANSI;"
Set cmdSQLData.ActiveConnection = cn
'query should return single value
Query = "select count(*) from claim_prod_view_db.claim_detail where claim_close_dt>='2009-07-15';"
cmdSQLData.CommandText = Query
cmdSQLData.CommandType = adCmdText
cmdSQLData.CommandTimeout = 0 'No timeout
Set rs = cmdSQLData.Execute()
'i'm assuming the value is present in rs, but i don't know how to verify
cn.Close 'no errors through this point
'i'm assuming here i need to make an actual connection to my Access file, 'although it's already open???
Dim Cnn As ADODB.Connection
Set Cnn = New ADODB.Connection
Cnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\Documents and Settings\434409\Desktop\Desktop Folders\Retail Automation\Retail.mdb;"
Cnn.Open
'test1 already exists in the .mdb
Cnn.Execute ("DELETE * FROM test1;") 'this works
'this is where i have the problem, how do i get the value from my teradata 'query into the test1 table??? this is where i'm stuck
Cnn.Execute ("INSERT INTO test1(count) select * from ???") 'i'm sure i'm 'way off here
Cnn.Close
End Function
Appreciate any direction anyone can give me, thanks.