ADO Connect string

[Rosco]

I am trying to connect to an Oracle 9 database with the following code, and I
keep getting this error:

Run Time Error '-2147217900(80040e14)':
[Microsoft][ODBC driver for Oracle][Oracle]ORA-00911: invalid character

Code follows:
Function GetData()

Dim adoConnect As ADODB.Connection, rs As ADODB.Recordset, sqlString As
String
Dim strConnectString As String

strConnectString =
"Provider=MSDASQL;DSN=myDSN;UID=xxx;PWD=yyy;SERVER=myOrcServer;"
sqlString = "SELECT Field1,Field2,Field3 INTO tblNewTable FROM
MyOrcTable"

Set adoConnect = New ADODB.Connection
Set rs = New ADODB.Recordset

adoConnect.Open strConnectString
rs.Open sqlString, adoConnect, adOpenStatic, adLockOptimistic

adoConnect.Close
Set rs = Nothing
Set adoConnect = Nothing

End Function

I believe I have the necessary references selected.
Anybody have any thoughts on what I am doing wrong?

Thanks in advance
Rosco

 

[Rosco]

Forgot to mention the error is occuring on this command:
rs.Open sqlString, adoConnect, adOpenStatic, adLockOptimistic

 

[Douglas J. Steele]

MSDASQL is the provider for SQL Server, not for Oracle.

Take a look at what Carl Prothman has at
http://www.carlprothman.net/Default.aspx?tabid=87 for appropriate connection
strings to use for Oracle.

 

[Rosco]

Well, that makes sense. Sometimes you can't see the forest for the trees.

Thanks Doug
Rosco

MSDASQL is the provider for SQL Server, not for Oracle.

Take a look at what Carl Prothman has at
http://www.carlprothman.net/Default.aspx?tabid=87 for appropriate connection
strings to use for Oracle.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I am trying to connect to an Oracle 9 database with the following code, and
I
keep getting this error:

Run Time Error '-2147217900(80040e14)':
[Microsoft][ODBC driver for Oracle][Oracle]ORA-00911: invalid character

Code follows:
Function GetData()

Dim adoConnect As ADODB.Connection, rs As ADODB.Recordset, sqlString As
String
Dim strConnectString As String

strConnectString =
"Provider=MSDASQL;DSN=myDSN;UID=xxx;PWD=yyy;SERVER=myOrcServer;"
sqlString = "SELECT Field1,Field2,Field3 INTO tblNewTable FROM
MyOrcTable"

Set adoConnect = New ADODB.Connection
Set rs = New ADODB.Recordset

adoConnect.Open strConnectString
rs.Open sqlString, adoConnect, adOpenStatic, adLockOptimistic

adoConnect.Close
Set rs = Nothing
Set adoConnect = Nothing

End Function

I believe I have the necessary references selected.
Anybody have any thoughts on what I am doing wrong?

Thanks in advance
Rosco