UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Using Adodb To Import Data, Access 2016    
 
   
AlBilly
post Sep 12 2017, 10:00 AM
Post#1



Posts: 105
Joined: 23-September 16



Hi,

I have a connection to an oracle server and I need to import some data into an access table via a SQL string but having problem with the syntax or else since I get the following "ORA-00942: table or view does not exist"
this is what I have so far.
CODE
dim conn as new adodb.connection
set conn = "Provider=OraOLEDB.Oracle;Data Source=TEST;User Id=TEST;password=TEST;"
If conn.State = adStateOpen Then
    SQL =INSERT INTO tblSuppInfoCommMethod (SUPPLIER_ID, blablabla...)" & _
" FROM PROD.VEND, PROD.VEND_SEG, PROD.VEND_ADDR, PROD.ADDR, PROD.VEND_ADDR_FUNC_REL, PROD.VEND_EMAIL_ADDR, PROD.VEND_COMMCN" & _
" WHERE VEND.VEND_NO = VEND_SEG.VEND_NO and other stuff...."
conn.execute SQL


the SQL string is very long so the reason of the blablabla and other stuff...
THe connection is working since I don't get any errors so I assume it is with the syntax or something else
Can anyone point me into the proper way of doing this

Thanks



Go to the top of the page
 
theDBguy
post Sep 12 2017, 10:03 AM
Post#2


Access Wiki and Forums Moderator
Posts: 70,629
Joined: 19-June 07
From: SunnySandyEggo


It must be typo since you're missing an opening quote for your SQL statement. However, have you tried a using simple SQL statement first just to check if the connection is working or not?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
AlBilly
post Sep 12 2017, 10:52 AM
Post#3



Posts: 105
Joined: 23-September 16



the SQL I wrote is just to fill the blanks
I have tried with the following very basic SQL
CODE
'import data
If conn.State = adStateOpen Then
    'delete data
    strSQL = "DELETE FROM tblSuppInfoCommMethod"
    CurrentDb.Execute strSQL, dbFailOnError
    
    strSQL = "INSERT INTO tblSuppInfoCommMethod (SUPPLIER_ID)" & _
            " SELECT DISTINCT TO_CHAR(VEND.SIC_CD) as SUPPLIER_ID FROM PROD.VEND"

    conn.Execute strSQL
end if

same error msg


Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    19th September 2017 - 12:12 PM