I wish to transfer MS-Access tables to Oracle 12 on a regular basis for data conversion purposes.
I have established the necessary ODBC DSN and the VB connection string and it all works very nicely and fast.
strConnect = "ODBC;DSN=Insight;UID=myuid;PWD=mypwd;DBQ=127.0.0.1/XE;DBA=W;APA=T;EXC=F;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;BTD=F;BNF=F;BAM=IfAllSuccessful;NUM=NLS;DPM=F;MTS=T;MDI=F;CSR=F;FWC=F;FBS=64000;TLO=O;MLD=0;ODA=F;"
Set db = DBEngine.Workspaces(0).OpenDatabase("", False, False, strConnect)
My issue is that the target Oracle database has three different TableSpaces and my Access tables need to go into one of the three depending upon it's potential size.
At present they all end up in the USERS TableSpace.
Can I nominate the TableSpace to use in my connection string or will I have to execute an SQL script in Oracle to move them from USERS to the appropriate TableSpace?
Thanks in anticipation.