Adodb connection to different SQL SERVER DB

[colpo]

Hi,

My environment: ACCESS 2000 + SQL SERVER 2000.
I have front-end Access2000. adp and database in Sql Server.
I use Oledb adodb.connection from .adp to Sql Server.

I have only one front-end program (.adp) and there' the form and via
VBA -module user can change the connection to different Db (two
database - one for test use and the other for production for example
names TESTDB and PROD_DB - database are equally designed).

I change the connection programmaticly (to point correct database) and
almost everything goes correct in forms but in list-boxes where the
data is occupied by stored procedures, don't work the way I want. In
this case data comes from the database which are opened when I start
ACCESS -program

Is there the way to update also these stored procs to point to correct
Db ?

Or do I have to make to different .adp ?

Colpo

 

[Sylvain Lafontaine]

First, ADP 2000 is really buggy; you should use ADP 2003 (still buggy but
much less). Maybe what you are seeing is one of those bugs. Second, how
are you proceeding to make the change and how are you reading the values for
these list boxes?

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[colpo]

Hi,

Thanks Sylvain for your help. I don't have adp2003 but adp2007. I
would try that.
But here is my code which I use to to change connection.
I populate list box with store proc and in list box properties I have
Row Source (store proc name) and Row Source Type (Table/View/
StoredProc)

LstNames (properties)

RowSource = qryNames
RowSourceType= Table/View/StoredProc

In FrmConnect form (this form I change connection to different
database)

Private Sub Form_Open(Cancel As Integer)
Dim fOk As Boolean

' Ignore error if no active form
On Error Resume Next

Me.strServer = strServer
Me.lblDatabase.Caption = gDatabase
If strDatabase = "Test_DB;" Then
optDatabase.Value = 2
Else
optDatabase.Value = 1
End If

End Sub


Private Sub cmdOk_Click()
On Error GoTo Err_CmdOk_Click

' Here change database connection from Test--> Prod or vice versa

Dim fOk As Boolean



Select Case Me.optDatabase.Value
Case 2
strDatabase = "Test_db;"
Case Else
strDatabase = "Prod_db;"
End Select



gcnn.Close
Set gcnn = Nothing



If Not OpenConnection() Then
fOk = OpenConnection()

End If

 

[Sylvain Lafontaine]

I don't see any call to Application.CurrentProject.CloseConnection and
Application.CurrentProject.OpenConnection in your code; which is what you
are supposed to do if you want to change the backend server or database
connected to.

It looks like that you are using your own connection to populate your forms.

Question: how are you populating your forms (and reports) and what do you
see in the Tables and the Queries/Views/SP windows (the tables/views/SP for
the old or the new database) after you have called your procedure to change
the connection?

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)


Hi,

Thanks Sylvain for your help. I don't have adp2003 but adp2007. I
would try that.
But here is my code which I use to to change connection.
I populate list box with store proc and in list box properties I have
Row Source (store proc name) and Row Source Type (Table/View/
StoredProc)

LstNames (properties)

RowSource = qryNames
RowSourceType= Table/View/StoredProc

In FrmConnect form (this form I change connection to different
database)

Private Sub Form_Open(Cancel As Integer)
Dim fOk As Boolean

' Ignore error if no active form
On Error Resume Next

Me.strServer = strServer
Me.lblDatabase.Caption = gDatabase
If strDatabase = "Test_DB;" Then
optDatabase.Value = 2
Else
optDatabase.Value = 1
End If

End Sub


Private Sub cmdOk_Click()
On Error GoTo Err_CmdOk_Click

' Here change database connection from Test--> Prod or vice versa

Dim fOk As Boolean



Select Case Me.optDatabase.Value
Case 2
strDatabase = "Test_db;"
Case Else
strDatabase = "Prod_db;"
End Select



gcnn.Close
Set gcnn = Nothing



If Not OpenConnection() Then
fOk = OpenConnection()

End If