Solved

Changing VB Data Environment Connection

Posted on 2002-07-04
11
296 Views
Last Modified: 2012-05-05
Hi Guys,

I need to set the CommandText of a Connection in the Data Environment at runtime. Basically the Datasource, Username, Password, Initial Catalog are all read in from the registry and may vary depending on the SQL server that the application is running on.

The problem I am having is that when I set the connection string I get an error. I am using "DE1.Conn1.ConnectionString = "Blah Blah Blah...."

Thanks in advance...

GarrenB
0
Comment
Question by:garrenb
  • 5
  • 5
11 Comments
 
LVL 69

Accepted Solution

by:
Éric Moreau earned 100 total points
ID: 7129544
>>I set the connection string I get an error
Which error?

You need to set the ConnectionString before using it. This is why, this setting is normally done into a "Sub Main" into a standard module and the startup object of your project is set to "Sub Main".
0
 
LVL 1

Author Comment

by:garrenb
ID: 7129586
My Conn1.ConnectionString is :
Provider=MSDataShape.1;Persist Security Info=True;User ID=projectx;Password=projectx;Data Provider=SQLOLEDB.1
(this is set in the sub-main section)



I then change my CommandText in MyCmd to ReQuery the database :
For Each myCmd In DE1.Commands
    If myCmd.Name = "Cmd1" Then
        'Debug.Print myCmd.CommandText
         strSQL = "SHAPE {SELECT PopHeader.PopNo, PopHeader.SuppAcNo, PopHeader.PopDate, PopHeader.Ref, PopHeader.Notes, PopHeader.CarraigeNet, PopHeader.CarraigeVAT, PopHeader.CarraigeTotal, Suppliers.SupplierName, Suppliers.SupplierStreet1, Suppliers.SupplierStreet2, Suppliers.SupplierTown, Suppliers.SupplierCounty FROM PopHeader INNER JOIN Suppliers ON PopHeader.SuppAcNo = Suppliers.SupplierAcNo WHERE (PopHeader.PopNo = " & PopId & ")}  AS Cmd1 APPEND ({SELECT PopDetails.PopNo, PopDetails.PartNo, PopDetails.Qty, PopDetails.UnitCost, PopDetails.SubTotal, PopDetails.PlusVat, PopDetails.Total, StockHeader.Description FROM PopDetails INNER JOIN StockHeader ON PopDetails.PartNo = StockHeader.PartNo}  AS CmdChild RELATE 'PopNo' TO 'PopNo') AS CmdChild"

         myCmd.CommandText = strSQL


However when I execute the command :
     myCmd.Execute


I get an error :
     "Invalid Object Name - PopDetails"

If I don't attempt to change the Conn1.connectionString the above code works perfect.

It only crashes if I programmatically change the Conn1.ConnectionString
0
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 7129669
Are you opening the new connection?
Conn1.Open

Just before executing the command, print the value of the connection string to see which one is active.

0
 
LVL 1

Author Comment

by:garrenb
ID: 7129753
>>>Are you opening the new connection?
Yes

The value of the ConnectionString is as above:
Provider=MSDataShape.1;Persist Security Info=True;User ID=projectx;Password=projectx;Data Provider=SQLOLEDB.1
0
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 7129802
where is your "Initial Catalog"?
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 1

Author Comment

by:garrenb
ID: 7129805
I suppose I should say that the data environment already exists and has a data report attached to it.

I don't know if this makes any difference.
0
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 7129809
if you change the connection string before anything connects to it, there should be no problems.

Have you check for the "Initial Catalog"?
0
 
LVL 1

Author Comment

by:garrenb
ID: 7129826
emoreau :
in the Sub Main I used :
With DE1
        .Conn1.CursorLocation = adUseClient
        .Conn1.ConnectionString = "Provider......."
        .Conn1.Open
End With

Before I didn't use the CursorLocation bit. This seems to have worked. I'll try it out on a different server and if everything is ok - the points are yours...

GarrenB
0
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 7129848
I don't think it should make differences. Any news about "Initial Catalog"?
0
 

Expert Comment

by:kader2001
ID: 7129894
Try this code over there.. this will be used to set
the dataenvironment at run time... it works .. i  think
this codes actually links to data report... see to it
if u can gain any idea from this one...



If you want to set the command at runtime:

   'close the enviroment if open
   If DataEnvironment1.Connection1.State = adStateOpen Then
       DataEnvironment1.Connection1.Close
   End If
   
   'change the sql statement, this also reopens the dataenvironment!
   DataEnvironment1.Commands("Command1").CommandText = "select * from tblClause"
   
   'change the datafield is nessacary
   DataReport1.Sections("Section1").Controls("Text1").DataField = "ClauseName"
   
   'open the report
   DataReport1.Show vbModal
   
   'destroy the report, vb sucks in doing this for you and causes memory leaks
   Unload DataReport1
   
kader[cheers]
0
 
LVL 1

Author Comment

by:garrenb
ID: 7130747
kader2001:
Thanks for the code sample but:
My problem was that I couldn't force a different connection source for the DE to read from. Requerying the data was no problem.

emoreau:
The initial catalog was set OK.
I did get it to work...
What I had to do was estalish a valid connection in the DE using the DE. I could then change the user id, password, datasource & initial catalog to anything I wanted. Then my code would work & force a new connection.

So therefore the lesson learned is that I needed to set the DE up initially with a valid connection string, then force my code to change it with the entries read in from the registry.

Cheers,

GarrenB
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
DIR issue 7 51
Windows 10 start screen issues 9 51
Determine Range to Select 5 42
How to compare ms sql hashbytes results within vb6 5 55
Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now