Setting ConnectionString at runtime for DED

[P.S.W.]

I have a project that uses the DED & Data Reports, and for installation purposes, I need to set its ConnectionString at runtime (so I can incorporate the App.Path object)

I thought I could put this code in the Form_Load or DataEnvironment_Initialize event:

Dim dbConnectString As String

dbConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & App.Path & "\Database\data.mdb"

DataEnvironment1.DEConnection.ConnectionString = dbConnectString

However, when I consequently try to open a Data Report that accesses one of the commands I built under the DEConnection, the program crashes, giving me an inexplicable error: "Data Source name too long". This doesn't happen when I define the ConnectionString at design time, though.

How do I make this work?!?? Your help is appreciated.

[davidrobin]

I have tried and tried to simulate the problem you described by doing the following.

copied the code from your post to the Form_load event
added a DataEnvironment to my project
set the properties to connect to the biblio database I copied into the directory of the application.
added a command and set the table.
created a datareport using the fields from the command
set the properties in the properties window for the datareport of teh data source and datamember.
copied the connection string to the code in the form_load event and cleared the connection string in the properties window.

it all worked fine when I showed the report OK. So I really don't have a definite answer for you. Hopefully I have simulated what you have done correctly. My only query would be How have you set the data source and data member for the data report. as I said i have done it in the properties window rather than in code.

If you email me I will send you the vb project I set up so you can see how I set it all up, may be that will give you some insight in to why mine works. Sorry I can't help you any more precisely.

[HunterMcCray]

P.S.W.,

I think that I understand your problem, but I have not worked with ADO or the data environment at all, I am still stuck back in DAO; however, I think that your problem is similar to one I have had in DAO, and that is You do not know what directory the User will place his database in. The only workaround that I have been able to come up with for this problem is to create a ".Dat" file in the root directory of the default Drive (Generally: C:\ProgramInit.Dat). When the app opens, it opens this .Dat file and if there is no database path there or the database path is invalid or generates an error then it promts the user to find the database path using standard controls. Once the database path and name are established the data is stored in the .dat file and all of the paths in the controls that need them are updated in the various form's _load routines. In theory you should be able to change the properties of the data controls and make them persist, but I have been unable to do this, so I simply trap bad connection errors and have them go to the Connection Form....If you find a more eloquent solution, I would love to hear about it.

Hunter

[P.S.W.]

Hey guys! I figured out what I was doing wrong. I thought I would post it here for the benefit of anyone else who might run into the same problem. My solution was as follows:

1. I installed Service Pack 4; I don't know how much of an effect this had, but it changed the error message from "Data Source name too long" to "Data Source name not found". Probably not a big deal--the real fix is what I discovered below.

2. To test my code, I did this:

MsgBox dbConnectString
DataEnvironment1.DEConnection.ConnectionString = dbConnectString
MsgBox DataEnvironment1.DEConnection.ConnectionString

I was expecting to see the same ConnectionString displayed twice -- but no! Turns out the second MsgBox displayed the ConnectionString w/ the wrong Provider. The DED had ignored the first part of my ConnectionString.

When I went back to the DED property pages, and set the Provider at design time to Jet 4.0, everthing started working!

For whatever reason, the DED won't accept setting a Provider at runtime (at least for me anyways). If anyone has any further insight on this, I'd be happy to hear it.

[Moï]

Another reason might be the lenght of your string... it happens many times when you creat long sql request: if you use a string (which is limited to 255 characters) your string will not be long enough (even if your try with a variant). The only remedy: put your connectionstring directly in the control:

dbConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & App.Path & "\Database\data.mdb"

DataEnvironment1.DEConnection.ConnectionString = dbConnectString

becomes:

DataEnvironment1.DEConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & App.Path & "\Database\data.mdb"

Hope it can help you.