I am having difficulty with the overload constructor in Chapter 8. I have added the existing WDABase.vb file to my DB Migration Utility along with the code shown on pages 161-163. However, when I run the project I receive an error that ProjectTimeTracker could not be opened.

If I understand the overload constructor properly, I should have two "Public Sub New" in the WDABase class so it will open up SQL with the Public Sub New(ByVal...) and open up the MS Access ProjectTimeTracker database with Public Sub New(). However, when I enter the code as given on pages 161-163, it opens the connection for SQL and then seems to try to open ProjectTimeTracker from the txtDatabase textbox and not access the app.config file where the extension to my MS Access database is located. If I edit the Private Sub btnOpenConnection_Click(ByVal...) code on page 162 by replacing objData=New WDABase(strProvider...) with objData=New WDABase() it then accesses the Public Sub New() code that references the app.config file and the MS Access ProjectTimeTracker database is opened successfully ("Database opened" is shown), however, SQL is not connected.

I have looked over the code several times and cannot see how the code shown will be able to connect to your Access database and to SQL Server as stated in step 13. Any assistance would be appreciated!

As I'm not sure what your code looks like at this point, my first suggestion would be to download the sample code for the book and compare it with your code. If after doing this you still have questions please let me know.

Thearon

I downloaded the sample code and opened the DB Migration Utility. I then changed the DataSource file extension under the DB Migration Utility Properties > Settings to the location where the downloaded ProjectTimeTracker.mdb is located. However, I received the same error as before with my typed in code..."Cannot open database "ProjectTimeTracker" requested by the login. The login failed."

If I experiment and change the text in the txtDatabase textbox, then the error (as shown in the above paragraph) replaces "ProjectTimeTracker" with whatever word I put in the textbox. That is why I am confused as to why it is trying to find and open the word in the textbox and not the file extension in the Setting section / app.config file. Hopefully you can provide me with some further assistance since I am stuck at this point. Thank you!

The Open button on the form should be using the data in the form fields to open your ProjectTimeTracker database in SQL Server. When you click on the Groups or Projects buttons the code for those event handlers will open the Access database.

I hope that helps but if not then let me know.

Thanks,
Thearon

How does SQL Server know where to find my ProjectTimeTracker database since all that is entered into the Database textbox is "ProjectTimeTracker"? Is it creating a new database that will be used to transfer the Access database information into? Or is SQL Server opening the ProjectTimeTracker.mdb file? I apologize for the continued questions...

All of the fields on the form make up the individual parts of the connection string used by the OleDbConnection object. The Server field is the starting point and lets the OleDbConnection object know which server contains the installation of SQL Server. The Database field lets the OleDbConnection know which database on the specified server it should be trying to connect to. Finally, the Login Name and Password fields contain the credentials for logging into the database.

The SQL Server database must exist and was created in Chapter 1. If you have not created the database, then please review Chapter 1 for instructions for doing so.

Thearon

I did create the ProjectTimeTracker SQL database in Chapter 1 and it is shown in the Data Connections under Database Explorer. When I go into Modify Connection to Test Connection, it will only succeed if "Use Windows Authentication" is selected. If I select "SQL Server Authentication" and enter my user name and password then I get an error "Failed to generate a user instance of SQL Server. Only an integrated connection can generate a user instance. The connection will be closed." I used SQL Server Management Studio Express to give my user name all possible rights.

I changed the code from pg 161 to allow Windows Authentication (Integrated Security=SSPI) as you wrote in another forum thread. However, this still does not open the connection to the database even though windows authentication worked when I tested the connection as mentioned above. Is there an issue with my Advanced Properties in the Modify Connection form? Here are some of the current settings:
Application Name - .Net SqlClient Data Provider
Connection Timeout - 30
Encrypt - False
Integrated Security - True
Persist Security Info - False
TrustServerCertificate - False
AttachDbFilename - C:\...\ProjectTimeTracker.mdf
ContextConnection - False
Data Source - .\SQLEXPRESS
User Instance - True

Any help is appreciated.

This has me stumped. As I’m not able to see your configuration, it’s hard for me to tell what exactly is going on. I often find it very helpful to search Google for those odd errors and problems and that would be my suggestion here. If you find an answer please post it so other readers can benefit from this experience.

Thearon

I have figured out the problem with my connection and was able to migrate the Projects and the Groups from Access to SQL Server.

I deleted the old database and created a new database in SQL Visual Basic by using the Modify Connection form - I selected SQL Server Authentication and changed User Instance to "False" in the advanced settings and then entered my username and password.

I then went into SQL Server Management Studio Express and logged in using my username and password - not windows authentication. I then attached the database to the SQLExpress server under my username (right-click "Databases" and select "Attach"), named it "ProjectTimeTracker" as shown in the txtDatabase textbox and made my username the "owner." This seems to be the primary resolution to the problem.

Thank you for your assistance. 8 Chapters down...8 more to go. It will be interesting to see how many more road blocks I run into along the way! I guess that is what learning is all about...

I'm glad you figured out the problem and are able to move on. Thank you for posting your solution so others may benefit from it.

Thearon