Results 1 to 3 of 3
  1. #1
    tlittell is offline Novice
    Windows XP Access 2003
    Join Date
    Jul 2009
    Pittsburgh, PA

    populate table w/ external data

    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;"

    '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

    End Function

    Appreciate any direction anyone can give me, thanks.

  2. #2
    yatin is offline Novice
    Windows XP Access 2003
    Join Date
    Feb 2010
    I am also facing a very similar problem. Can anyone suggest anything?? I am stuck at the same point

  3. #3
    JohnBoy is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Feb 2010
    I know this sounds really simple and perhaps I have mis-read the problem but can you just not put together a make table query in your Access DB that uses the target DB as the source, I do this against a SQL Server DB to populate a table in an Access DB... ???

    I use alinked table set up to the source and then just run the Make Table query to extract the data I need..

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Populate date from previous record
    By Lianes in forum Access
    Replies: 3
    Last Post: 09-19-2009, 03:42 PM
  2. Populate Combo Box
    By wes28 in forum Programming
    Replies: 1
    Last Post: 03-04-2009, 06:45 AM
  3. auto populate and table relationships
    By jmarti57 in forum Access
    Replies: 0
    Last Post: 11-09-2008, 09:50 PM
  4. One table, different data
    By Zoroxeus in forum Reports
    Replies: 2
    Last Post: 12-07-2006, 04:33 PM
  5. Unmatched data entered with data in table
    By boreda in forum Access
    Replies: 0
    Last Post: 07-28-2006, 09:11 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
Other Forums: Microsoft Office Forums