Re: Open DB using OLE DB
From: Al Reid (areidjr_at_reidDASHhome.com)
Date: 05/03/04
- Next message: Tony McGuire: "Re: Image size"
- Previous message: Rick Rothstein: "Re: VB6, trig and numercial integration"
- In reply to: Fie Fie Niles: "Re: Open DB using OLE DB"
- Next in thread: Fie Fie Niles: "Re: Open DB using OLE DB"
- Reply: Fie Fie Niles: "Re: Open DB using OLE DB"
- Reply: Fie Fie Niles: "Re: Open DB using OLE DB"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 3 May 2004 13:20:44 -0400
Try the following:
Global g_adoConn As ADODB.Connection 'I do not recommend using New here
If Not g_adoConn is nothing then Set g_adoConn = Nothing
Set g_adoConn = New ADODB.Connection
With g_adoConn
.ConnectionTimeout = 120
.ConnectionString = "Provider=SQLOLEDB.1;Data Source=myserver;User ID=myUserid;Password=myPassword;"
.Open
End With
"Fie Fie Niles" <fniles@wincitesystems.com> wrote in message news:O1Tn%235SMEHA.2716@tk2msftngp13.phx.gbl...
> I am sorry for the confusion. I am using both SQL Server and Oracle. When I
> tried opening the SQL Server database, I use the SQLOLEDB.1 provider.
> I have not tried Oracle yet.
>
> Here is my code to open SQL Server database:
> Global g_adoConn As New ADODB.Connection
>
> With g_adoConn
> .ConnectionTimeout = 120
> .Open "Provider=SQLOLEDB.1;Data Source=myserver;Initial
> Catalog=myDatabase;" -> got error "Supplied provider is different from the
> one already in use"
>
> OR
> .Open "Provider=SQLOLEDB.1;Data Source=myserver;User
> ID=myUserid;Password=myPassword" -> got error "Supplied provider is
> different from the one already in use"
>
> End With
>
> Thanks.
>
>
>
> "Al Reid" <areidjr@reidDASHhome.com> wrote in message
> news:uyU7lwSMEHA.2456@TK2MSFTNGP12.phx.gbl...
> > I would really need more info. I assume that you are using MSSQL,
> although you alluded to Oracle as well.
> >
> > Can you post the code you are using to establish the connection.
> >
> > --
> > Al Reid
> >
> > "It ain't what you don't know that gets you into trouble. It's what you
> know
> > for sure that just ain't so." --- Mark Twain
> >
> > "Fie Fie Niles" <fniles@wincitesystems.com> wrote in message
> news:OIziQrSMEHA.3380@TK2MSFTNGP11.phx.gbl...
> > > Thanks.
> > > I got the error "Supplied provider is different from the one already in
> > > use." when I used either
> > > .Open "Provider=SQLOLEDB.1;Data Source=myserver;Initial
> > > Catalog=myDatabase;"
> > > or
> > > .Open "Provider=SQLOLEDB.1;Data Source=myserver;User
> > > ID=myUserid;Password=myPassword"
> > >
> > > How can I fix it? Thanks again.
> > >
> > >
> > > "Al Reid" <areidjr@reidDASHhome.com> wrote in message
> > > news:uzREvOSMEHA.128@TK2MSFTNGP09.phx.gbl...
> > > > You need to use the correct providers:
> > > >
> > > > Oracle:
> > > > cn.ConnectionString = "PROVIDER=MSDAORA;USER ID=" & UserName &
> > > ";PASSWORD=" & Password & ";DATA SOURCE=" & Server & ";"
> > > >
> > > > MSSQL:
> > > > cn.ConnectionString = "Provider=SQLOLEDB.1;Data Source=" & Server &
> ";User
> > > ID=" & UserName & ";Password=" & Password & ";"
> > > >
> > > > --
> > > > Al Reid
> > > >
> > > > "It ain't what you don't know that gets you into trouble. It's what
> you
> > > know
> > > > for sure that just ain't so." --- Mark Twain
> > > >
> > > > "Fie Fie Niles" <fniles@wincitesystems.com> wrote in message
> > > news:%23kcpbJSMEHA.340@TK2MSFTNGP11.phx.gbl...
> > > > > Thanks.
> > > > > When I tried the following codes below, I got an error
> "[Microsoft][ODBC
> > > > > Driver Manager] Data source name not found and no default driver
> > > specified".
> > > > > How can I fix it? Thanks again.
> > > > >
> > > > > Global g_adoConn As New ADODB.Connection
> > > > >
> > > > > With g_adoConn
> > > > > .ConnectionTimeout = 120
> > > > > .Open "Provider=MSDASQL;Data Source=myserver;Initial
> > > > > Catalog=myDatabase;"
> > > > > End With
> > > > >
> > > > >
> > > > > "Jeff Johnson [MVP: VB]" <i.get@enough.spam> wrote in message
> > > > > news:Oi5NvsRMEHA.2676@TK2MSFTNGP12.phx.gbl...
> > > > > >
> > > > > > "Fie Fie Niles" <fniles@wincitesystems.com> wrote in message
> > > > > > news:%238$$LfRMEHA.3572@tk2msftngp13.phx.gbl...
> > > > > >
> > > > > > > Could you please tell me if this is the correct way to open a
> > > database
> > > > > > using
> > > > > > > OLEDB?
> > > > > >
> > > > > > > Global g_adoConn As New ADODB.Connection
> > > > > > >
> > > > > > > With g_adoConn
> > > > > > > .ConnectionTimeout = 120
> > > > > > > .Open "Provider=MSDASQL;Data Source=" & sDSN & ";User ID="
> &
> > > sUID
> > > > > &
> > > > > > > ";Password=" & sPWD
> > > > > > > End With
> > > > > > >
> > > > > > > Do I still need to go to Control panel - Administrative Tools -
> Data
> > > > > > Sources
> > > > > > > (ODBC) and create a data source using SQL Server driver or
> Microsoft
> > > > > ODBC
> > > > > > > For Oracle ?
> > > > > >
> > > > > > It will WORK, but I doubt you'll find anyone around here who would
> > > call it
> > > > > > the CORRECT way. We prefer DSN-less connections. For that, you
> just
> > > add
> > > > > >
> > > > > > Data Source=<server name>;Initial Catalog=<database name>;
> > > > > >
> > > > > > to the connection string. (Some folks prefer to use the older ODBC
> > > > > keywords
> > > > > > of SERVER= and DATABASE=, but I say if you're going to do it the
> OLE
> > > DB
> > > > > way,
> > > > > > do it all the way.)
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>
- Next message: Tony McGuire: "Re: Image size"
- Previous message: Rick Rothstein: "Re: VB6, trig and numercial integration"
- In reply to: Fie Fie Niles: "Re: Open DB using OLE DB"
- Next in thread: Fie Fie Niles: "Re: Open DB using OLE DB"
- Reply: Fie Fie Niles: "Re: Open DB using OLE DB"
- Reply: Fie Fie Niles: "Re: Open DB using OLE DB"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
