Creating SQL Server database with Visual Basic 2005 Express

kernan · #1 (**permalink**) December 28th, 2008, 08:31 AM

I have been using Thearon Willis's book 'Beginning Visual Basic 2005 Databases' to learn about using SQL Server 2005 Express instead of Access 2000, which database I have used in the past, and as a hobbyist programmer, I have found the book very useful indeed.

At the outset, I have to admit that instead of using the project in the book, I am trying to adapt the methods to an old project of my own which used Access 2000 as its database. At first everything worked fine and I found a whole new way of dealing with Data Access using the WBAccess base class.

However, when I tried to migrate my database to SQL as described in Chapter 8, I ran into a number of problems mainly brought on by the fact that I am using the Express Editions of both Visual Basic 2005 and SQL Server 2005. Some of these problems, such as Windows Authentication, I managed to solve by reading through the threads opened by some of the contributors to this forum but one I can't solve:-

I created an empty SQL Server database using Visual Basic 2005 Express, which works differently from the process descibed in Chapter 1. (This included 14 tables , primary keys and relationships!) Unfortunately VB Express appears to produce something called a User Instance, which I don't really understand, and as such, I can't connect to it through the usual Server - (local)\SQLExpress - in the migration program. I have tried using the Server Management Studion Express to attach the empty SQL database to my Server but without success. No doubt, had I been following the text in your book to the letter and created the ProjectTimeTracker database, the same problem would have arisen.

I would appreciate any help you can offer to allow me to continue to explore the rest of this excellent book.

Old Pedant · #2 (**permalink**) December 28th, 2008, 05:46 PM

I can only tell you what worked for me.

I created the DB using the free "Management Studio Express".

*THEN* I connected to it from VB Express. And then I could use VB to create tables in that DB and the tables are visible quite nicely from Management Studio Express, from other SQL Server clients, and of course from VB.

So it's only the need to create the DB outside of VB that may be hanging you up. Maybe.

kernan · #3 (**permalink**) December 29th, 2008, 07:58 AM

Many thanks for your prompt reply, Old Pedant.

What you suggest sounds like a good way of getting round the problem. I haven't tried it yet since, in the meantime, I found another solution - I used the free Microsoft SQL Server Migration Assistant for Access and succeeded in achieving the same end result, except that the resulting SQL database was accessible from both Microsoft SQL Server Management Studio Express and VB Express.

Nevertheless, out of curiosity, I will try your solution in the near future.

Again, many thanks.

I have now tried your suggestion but there are still problems:

I first created a new database in SSMSE but VB wouldn't connect with it, so I detached it from my Server. This allowed VB to connect and I used VB to create, yet again, all the tables and relationships. I then re-attached the database to my Server using SSMSE again. (I had to exit VB before this was possible.)

Then back to the Migration program: It still will not connect - I get the message "Cannot open database 'MyDB' requested by the login. The login failed."

What is wrong?

Old Pedant · #4 (**permalink**) December 29th, 2008, 05:20 PM

Dunno what to tell you. As I said, it *DID* work for me, doing it the way I described.

Granted, it took me a couple of tries to figure out the right connection string to use (VB wouldn't connect using its own defaults), but once I got the connection string right it worked just fine.

kernan · #5 (**permalink**) December 30th, 2008, 10:45 AM

Hi Old Pedant

Thanks again for your interest. Good news! I finally got it to work. Bad news! I don't remember how - I made so many changes, moved things around, renamed files, detached and re-attached databases, etc. that I lost track completely. However, the outcome was worth it.

There is one point I'd like to make however. One of the tables I was migrating was empty and this caused an error in the 'Finally' section of the published code due to the fact that because there were no rows in the DataReader, the objData.command didn't exist, so it couldn't be disposed! I changed the code so that the 'cleanup' was contained within the 'If' - 'End If' section as below and removed the 'Finally' section completely:

Code:

If .DataReader.HasRows Then
......
'Process all rows
While .DataReader.Read
.......
'Execute the INSERT statement
objData.Command.ExecuteNonQuery()
EndWhile
'cleanup
objData.Command.Dispose()
objData.Command = Nothing
EndIf
EndWith
'close the DataReader
objAccessDB.DataReader.Close()
.......

This avoids the problem. Maybe someone will find it useful!

Need to download code?

Navigation