• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1960
  • Last Modified:

TransferDatabase acImport- Prevent ODBC Oracle Login popup

Part of my code includes transfering tables from an Oracle database to an Access databse.

I have established ODBC connections and using code to pass the login and password info.

However, the login popup happens preventing me from complete automatiion.

Any suggestions.

DoCmd.TransferDatabase acImport, "ODBC Database", _
    "ODBC;DSN=MTR2;UID=" & fOSUserName & ";PWD=" & fOSUserName & ";LANGUAGE=us_english;" _
    & "DATABASE=MTR2", acTable, " & Chr(34) & nTable & Chr(34) & ", " & Chr(34) & nTable & Chr(34) & "", True"
        SendKeys "~"

Note I even tried sendkeys - clumszy and it really doesn't work correctly.

Thanks.
Karen
0
Karen Schaefer
Asked:
Karen Schaefer
  • 7
  • 5
  • 4
1 Solution
 
Eric ShermanAccountant/DeveloperCommented:
Try it without the UID and PWD parameters ...

ET
0
 
rockiroadsCommented:
Is your password the same as your username?

what is your password here
UID=" & fOSUserName & ";PWD=" & fOSUserName


If u enter a valid username/password, I dont think u get a logon prompt

0
 
Karen SchaeferAuthor Commented:
Yes, I still get the logon prompt even if I pass the userid and pswd.

Yes, the userid and password are the same in this case.

I also have run into the issue that If I use the a macro to transfer the data import  the import works, however, I still get the popup.

Sample1:
     DoCmd.TransferDatabase acImport, "ODBC", "ODBC;DSN=MTR2;UID=JGONZALEZ;DBQ=MTR2 ;DBA=W;APA=T;EXC=F;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;GDE=F;FRL=F;BAM=IfAllSuccessful;MTS=F;MDI=F;CSR=F;FWC=F;PFC=10;TLO=0;;TABLE=APPUSER.MSCTR_DY", acTable, "APPUSER.MSCTR_DY", "APPUSER_MSCTR_DY", False

I have tried various ways to establish the Oracle ODBC connection - the first one I don't get the popup but I also am unable to import the table.

Sample2:
Do Until rs.EOF = True
  i = i + 1
    nTable = rs.Fields("Name")
    nDev = Replace(Left(rs.Fields("Name"), InStr(rs.Fields("Name"), "_")), "_", ".") & Mid(rs.Fields("Name"), InStr(rs.Fields("Name"), "_") + 1)

    On Error Resume Next
    If IsObject(currentdb.TableDefs(nTable)) Then
        DoCmd.DeleteObject acTable, nTable
    End If
   
   strConnection = "ODBC;DSN=MTR2;UID=" & fOSUserName & ";PWD=" & fOSUserName & ";DATABASE=MTR2"
   'Create a new ADO Connection object
   Set cn = New ADODB.Connection

   With cn
      .Provider = "MSDASQL"
      .Properties("Data Source").Value = strConnection
      .Open
   End With
DoCmd.TransferDatabase acImport, strConnection, acTable, Table = " & Chr(34) & ndev & Chr(34) & ", " & Chr(34) & nDev & Chr(34) & ", " & Chr(34) & nTable & Chr(34) & """""


Please review and make suggestons.

Karen
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
rockiroadsCommented:
Ive an example at work as I did it recently, I dont recall getting a logon prompt though, the only prompt I got was to identify key fields. I used TransferDatabase

Just looking at your ODBC line

Where is your PWD line?

Try making your code simpler


DoCmd.TransferDatabase acLink,
"ODBC Database", "ODBC;DSN=MTR2;UID=JGONZALEZ;PWD=XXXX;SERVER=TNSNAME", acTable, "APPUSER.MSCTR_DY", "ACCESSLocalTable", False, False

Note
PWD=XXX  - XXX is your password
Server=TNSNAME  - TNSNAME setting is the Oracle TNS Name (one u setup in ODBC)



0
 
Eric ShermanAccountant/DeveloperCommented:
Karen ...

Have a look at this previous question.  I think this is what you are looking for.

http://www.experts-exchange.com/Databases/MS_Access/Q_10076194.html?query=password+prompt+on+ODBC+Connection&clearTAFilter=true


ET
0
 
Karen SchaeferAuthor Commented:
I don't get the popup - unfortunately, I also does not import the tables.

Your code was for linking - I need to import the tables and the name of the tables will change as it looks thru the code so I need to use variables for the table names - plus revised table name with the ".".

Thanks,

K
0
 
Eric ShermanAccountant/DeveloperCommented:
This is the solution that was accepted for the link I posted above.  The member coffeeshop provided this solution and it seems very similar to your question.

I use the following function, when the user first start the application. This provides
access to all attached tables, without prompting the user for id or password.
(you should always use the newest ODBC-driver from Oracle, the most I know
does not work correct)

Function ConnectToODBC (ByVal DBUser As String, ByVal DBPassword As String) As Integer

  Dim dbo As Database
  Dim odbc_con As String

  'connect-string
  odbc_con = odbc_con & ";WSID=" & UserName & ";UID=" & DBUser & ";PWD=" & DBPassword

  Set dbo = ws.OpenDatabase("", False, False, odbc_con)
 
  'optional
  dbo.QueryTimeout = 0

  'connect
  odbc_con = dbo.Connect
 
  'close does not close the odbc-connection
  dbo.Close
 
End Function

ET
0
 
Karen SchaeferAuthor Commented:
thanks ET for the suggestion, this works great - except I am still unable to actual transfer the database tables into ACCEss.  

I am still not getting the tables to import into my access database
  nTable = rs.Fields("Name")
    nDev = Replace(Left(rs.Fields("Name"), InStr(rs.Fields("Name"), "_")), "_", ".") & Mid(rs.Fields("Name"), InStr(rs.Fields("Name"), "_") + 1)

DoCmd.TransferDatabase acImport, "ODBC Database", "ODBC;DSN=MTR2;UID=JGONZALEZ;PWD=JGONZALEZ;SERVER=MTR2", acTable, " & Chr(34) & ndev & Chr(34) & ", " & Chr(34) & ntable & Chr(34) & ", False, False

What am i missing - when I do it wiith the Macro it works as far as importing the table.

K
0
 
rockiroadsCommented:
Is it not possible for you to create a ADO connection into Oracle
then loop through the recordset
for each record read, insert values into a table

alternatively, use the Oracle dump utility, u can export to a csv file, then import that csv file
0
 
rockiroadsCommented:
If u get no popups with the original code I gave

what if u tried changing acLink to acImport?

DoCmd.TransferDatabase acImport,
"ODBC Database", "ODBC;DSN=MTR2;UID=JGONZALEZ;PWD=XXXX;SERVER=TNSNAME", acTable, "APPUSER.MSCTR_DY", "ACCESSLocalTable", False, False

u still passing in uid and specify tns name
0
 
Karen SchaeferAuthor Commented:
that work except I need to pass the table names using the variables of ndev and ntable.

Due to the fact that I am using a list of table names that I am loop thru to import.

k
0
 
rockiroadsCommented:
ok, then we just substitute the values

DoCmd.TransferDatabase acImport, "ODBC Database", "ODBC;DSN=MTR2;UID=JGONZALEZ;PWD=JGONZALEZ;SERVER=MTR2", acTable, " & Chr(34) & ndev & Chr(34) & ", " & Chr(34) & ntable & Chr(34) & ", False, False


Your variable to hold table is called nTable?

Previously define
sUID = "JGONZALEZ"
sPWD = "XXX"
sServer = "SOMETNSNAMEENTRY"
sDSN = "MTR2"


DoCmd.TransferDatabase acImport, "ODBC Database", "ODBC;DSN=" & sDSN & ";UID=" & sUID & ";PWD=" & sPWD & ";SERVER=" & sServer, acTable, nTable, nTable, False, True


The last parameter True, stores the login, which is what I think u want
0
 
rockiroadsCommented:
what is the oracle table called? what is the local table called?
Hopefully u can make sense of what ive done

the first nTable use is the name of the oracle table
the second is what u want to call the access table

0
 
Eric ShermanAccountant/DeveloperCommented:
Ok, this should correct your problem ...  The ndev and ntable variables are already string, no need to enclose them in quotes.

DoCmd.TransferDatabase acImport, "ODBC Database", "ODBC;DSN=MTR2;UID=JGONZALEZ;PWD=JGONZALEZ;SERVER=MTR2", acTable, ndev, ntable, False, False


ET
0
 
Karen SchaeferAuthor Commented:
THANK YOU THANK YOU THANK YOU - THIS FINALLY GOT ME WHAT I WAS LOOKING FOR.


THANKS,

kAREN
0
 
rockiroadsCommented:
No worries

Karen, I posted an example of using CDO in sending emails. It regards your post of sending emails with a security message. CDO can be used to bypass that.
Let me know if it works for you or not
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

  • 7
  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now