Access or SQL

[VBOfficer]

Hello,

I have written an address book and for its database, I used Access to create a .mdb database and store my data programmatically into this .mdb file.

However, the problem is that when reading and saving data to this kind of database I have to use Jet 4.0 which is NOT available on 64-bit systems and I have to compile my application as 32-bit in order to run on all systems.

I just want to know how is upgrade to SQL?

I never used SQL, can I create a stand-alone database file using SQL like Access and create tables/fields so I can modify it programmatically?

I also need the database file to be independent and don't need any special requirements to run on target systems, like the .mdb file I've created in Access!

Please advise :)

[PlausiblyDamp]

Have you considered SqlExpress or SQL Server Compact as an alternate? Both are 32 / 64 bit compatible and can use the sql .mdf file as a stand alone file without needing sql to be installed as a service.

[VBOfficer]

Thanks, well I use Microsoft.Jet.OLEDB.4.0 to work with Access files, this driver has NOT a 64-bit version.

I decided to switch to SQL Server Express 2008. So in my VB.NET 2008 with .NET Framework 2.0, what driver/referece should be used?

I think my code should remain intact or with minor changes, since I use SQL commands and they are the same in both?

 

--

 

For example this is one of my codes:

Dim StringConnection As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=File.mdb;Jet OLEDB:Database Password = 12345"
Dim MyConnection As New OleDbConnection(StringConnection)
MyConnection.Open()
Dim ReadUsers As New OleDb.OleDbCommand("SELECT Name, Email, Title FROM Accounts", MyConnection)
Dim MyReaders As OleDbDataReader = ReadUsers.ExecuteReader
While MyReaders.Read
  MessageBox.Show MyReaders("SendingMethod").ToString
End While
MyReaders.Close()
MyConnection.Close()

[PlausiblyDamp]

It would use System.Data.SqlCLient classes rather than the OleDb classes and the connection string would point to a sql server rather than be an ole db provider pointing to a jet database but the actual sql commands are by and large identical for the more common tasks.

[VBOfficer]

Hi and thanks for your professional comments :)

I am so glad you are here to help other people, God bless you! :p

Everything is going well, I have installed SQL Server Express 2008 and am re-creating my database and its tables...

However, there is one thing I cannot find it in the new environment.

 

In Access, when I create a new table, I am being asked to create a Primary Key, here's full message box:

-

There is no primary key defined.

Although a primary key isn't required, it's highly recommended. A table must have a primary key for you to define a relationship between this table and other tables in the database.

Do you want to create a primary key now?

-

I MUST press YES to create a Primary Key, how to do this in SQL Server 2008?

You know, Access automatically creates an "Auto Number" ID <Long Integer> and set it Primary Key.

While SQL Server 2008 won't do this or I cannot find it?

There is a Primary Key column for all fields but this does not seem to be what I want?

 

Can you please be so kind and help me once more?! :)

[PlausiblyDamp]

To get the same effect as an autonumber field in sql simply declare the column has being an integer and then set it's identity property to true.

 

You can set the primary key by clicking on the little key button when the correct column is selected in the designer.

[VBOfficer]

Hi again:)

Do you have any idea what's the replacement for Memo data type in SQL Server?

Memo type does not have a field size... But it cannot be found in SQL Server 2008... :confused:

[PlausiblyDamp]

Text or NText depending on if you need Unicode support (NText is the unicode version) - has a storage limit of 2G per field.

[VBOfficer]

Well, there is no Text, and ntext has length of 16 and length is not changeable...:confused:

[VBOfficer]

Hi PlausiblyDamp,

Thanks for all your help :)

Anyway there's a question in my mind if it's possible to read .sdf file from my VB.NET application on target systems WITHOUT SQL Server 2008 installed on them? Either it's not possible or I cound not figure its connection string after a lot of search on the web... :(

[PlausiblyDamp]

Did you try using NText as a datatype?