Hod do I Create an ODBC in VB. (1 Viewer)

[SeanP]

Can someone please help me create code that when called it create an ODDC connection with an Oracle driver in an Access database. I have code that will connect but i really need one that will create it when called.


Function OracleConnect() As Boolean
Dim ws As Workspace
Dim db As Database
Dim LConnect As String
On Error GoTo Err_Execute
'Use {Microsoft ODBC for Oracle} ODBC connection
'CreateWorkspace
LConnect = "ODBC;DSN=LAHP1;UID=XXX;PWD=XXXX;SERVER=dclahop0"
'Point to the current workspace
Set ws = DBEngine.Workspaces(0)
'Connect to Oracle
Set db = ws.OpenDatabase("", False, True, LConnect)
db.Close
OracleConnect = True
Exit Function
Err_Execute:
MsgBox "Connecting to Oracle failed."
OracleConnect = False
End Function

 

[joh024]

Can someone please help me create code that when called it create an ODDC connection with an Oracle driver in an Access database. I have code that will connect but i really need one that will create it when called.


Function OracleConnect() As Boolean
Dim ws As Workspace
Dim db As Database
Dim LConnect As String
On Error GoTo Err_Execute
'Use {Microsoft ODBC for Oracle} ODBC connection
'CreateWorkspace
LConnect = "ODBC;DSN=LAHP1;UID=XXX;PWD=XXXX;SERVER=dclahop0"
'Point to the current workspace
Set ws = DBEngine.Workspaces(0)
'Connect to Oracle
Set db = ws.OpenDatabase("", False, True, LConnect)
db.Close
OracleConnect = True
Exit Function
Err_Execute:
MsgBox "Connecting to Oracle failed."
OracleConnect = False
End Function

I actually just designed something similar to this. This will create a passthrough query. make a table using that query then delete both temp queries.

mySQL is a PL/SQL statement if you are using and oracle driver. tblNm is where you want the data to go. connect is the oracle connection string; if you don't know what it is exactly just create a passthrough manually and copy it from the properties.

Public Function PassThrough(mySQL As String, tblNm As String, ByVal connect As String)
Dim Dbs As Database
Dim PassThrough As QueryDef
Dim CreatTbl As QueryDef
Set Dbs = CurrentDb()
 
''Deletes a previously failed attempt
For Each q In Dbs.QueryDefs
    If q.Name Like "PassThroughQry" Or q.Name Like "TempQry" Then
        Dbs.QueryDefs.Delete (q.Name)
    End If
Next
 
''Create new query
Set PassThrough = Dbs.CreateQueryDef("PassThroughQry")
 
''Set connect string
PassThrough.connect = connect
 
''Insert SQL Statement
PassThrough.SQL = mySQL
 
''Inform access that the query returns records
PassThrough.ReturnsRecords = True
 
With Dbs
      ''Create query on access side.
      Set createtbl = .CreateQueryDef("TempQry", "SELECT * INTO [" & tblNm & "] FROM PassThroughQry")
    
    ''Remove the timeout warning
    createtbl.ODBCTimeout = 0
    
    ''Run query
    DoCmd.OpenQuery "Tempqry"
 
     ''Delete Query
    .QueryDefs.Delete "TempQry"
End With
Dbs.QueryDefs.Delete "PassThroughQry"
Dbs.Close
Set Dbs = Nothing
Set PassThrough = Nothing
Set createtbl = Nothing
End Function

 

[joh024]

I think that I may have misunderstood what you needed when I first read your post. Not sure exactly how but look into the opendatabase method in the help for an example of what to use.

 

[joh024]

I think that I may have misunderstood what you needed when I first read your post. Not sure exactly how but look into the opendatabase method in the help for an example of what to use.

Here is the best I could come up with. It builds the connection but it will still propt the login box if the uid or password are incorrect.

Dim db As DAO.Connection
    Dim ws As Workspace
    
    Dim oracleConnect, redBrickConnect As String
    oracleConnect = "ODBC;DSN=MyOdbcDriverName;DBQ=MyOdbcDriverName;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;"
    
Set ws = CreateWorkspace("MyOdbcDriverName", "UserID", "password", dbUseODBC)
    
    Set db = ws.OpenConnection("SSD", , , oracleConnect)

 

[SeanP]

The password will never expire. What are "DBQ" and "SSD"? Where do I put in the TNS servcie name?

When i look at my manually created ODBC connection, the diver name is "Oracle ODBC Driver" do I put that in for the DBQ?

Thank you for your help!!!!

 

[joh024]

The password will never expire. What are "DBQ" and "SSD"? Where do I put in the TNS servcie name?

When i look at my manually created ODBC connection, the diver name is "Oracle ODBC Driver" do I put that in for the DBQ?

Thank you for your help!!!!

The DBQ is whatever is in the data source name field when you look at your driver in the ODBC manager. So when you go to the Administrative tools/ Data sources it is whatever is in the "Name" column. The SSD was something I forgot to take out; that is the name of the database that you want to connect to.

If you were to link to the tables in access this would be whatever precedes the first "_" in the table name.

 

[SeanP]

I know I am close, i don't get an error when I run this but it doesn't show up in my ODBC datasource admin window. it being the new connection I am trying to create. LAHP2 is th connection I am trying to create

Function test() As Boolean
Dim db As DAO.Connection
Dim ws As Workspace

Dim oracleConnect, redBrickConnect As String
oracleConnect = "ODBC;DSN=LAHP2;DBQ=OP0;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;"

Set ws = CreateWorkspace("OP0", "ID", "PWORD", dbUseODBC)

' Set db = ws.OpenConnection("LAHP2", , , oracleConnect)
End Function

 

[joh024]

I know I am close, i don't get an error when I run this but it doesn't show up in my ODBC datasource admin window. it being the new connection I am trying to create. LAHP2 is th connection I am trying to create

Function test() As Boolean
Dim db As DAO.Connection
Dim ws As Workspace

Dim oracleConnect, redBrickConnect As String
oracleConnect = "ODBC;DSN=LAHP2;DBQ=OP0;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;"

Set ws = CreateWorkspace("OP0", "ID", "PWORD", dbUseODBC)

' Set db = ws.OpenConnection("LAHP2", , , oracleConnect)
End Function

I have never actually used this so I was just poking around with it. There a function that you can use to test called stillExecuting . this returns true if the connect has not finished. So you can loop while it tries. just a

while db.stillexecuting
''Do nothing
wend

would work maybe.

 

[SeanP]

Ok thank you for the help.