Migrating from Ms Access to SQL Server 2000
Hi,
Is there many changes need t o be done in order to change my DBMS. Currently, i'm using Microsoft Access in my VB.net program. however, i need to change the DBMS to SQL Server. So what should i do can i still use back the same coding what namespace should i include
Yes.. thanks a lot fren! it works! i have successfully connected to the sql server through my program. Really appreciates your helpful deed. thanks a lot. Thank you.
as u ask me to check the permission by query analyzer and i have done so. no prob loging into the server using window authentication.
I think you pretty well covered all bases there.
Even with the connection string it is going to be difficult to assess which one of these applies, unless of course she is using the exact connection string provided and not transposed the values to her actual values.
Hi there,
What does your connection string look like now It appears as though you still have "master.mdf" somewhere in your connection string but you shouldn't have this.
Based on your previous connection string, I think your connection string should look like:
Server=AGMB6PIMO1668;Database=Master;Integrated Security = SSPI
Note that the database name I have used is "Master". You do not need the .mdf extension or a path to the file or anything, just the name of the database.
Have a go, hope that helps a bit.
Hi there,
It will help if you have SQL Server Enterprise Manager open for this. Make sure that the SQL Server you want to connect to is registered in Enterprise Manager so that you can see it in the Enterprise Manager GUI.
Try changing the following:
- "Server=\\AGMB6PIMO1668" The name of the SQL Server does not need the double slash (\\) before the server name. Change this to Server=AGMB6PIMO1668. Also ensure that AGMB6PIMO1668 is the name of the SQL Server...If the SQL Server is a default instance and AGMB6PIMO1668 is the name of the machine on which it is installed then you should be OK
- "Database=C:\Program Files\Microsoft SQL Server\MSSQL\Data\master.mdf" The database parameter should be the name of the actual database, not the path to the database file. In your case, as you seem to be referencing a System Database, you should put Database=Master
- Not really something to change but something to check. Your connection string indicates that you are trying to connect using Windows Authentication. You should check that your Windows account can log into the SQL Server.
This is something you can check by using Query Analyser. You can do this by:
1) Open a new Query Analyser session by going Start > All Programs > Microsoft SQL Server > Query Analyser
2) Specify the SQL Server to connect to as AGMB6PIMO1668 and tell it to use Windows Authentication (should be a radio button for this)
3) If you can connect OK then your Windows Account can log into the SQL Server. Whether it has the appropriate permissions to the databases you want to work with is something that you will have to check yourself.
Hope that helps a bit
Migrating from Ms Access to SQL Server 2000
Migrating from Ms Access to SQL Server 2000
KenWilson
Firstly, I would consider using the Access Upsizing Wizard to migrate your database from MS Access to SQL Server if you have not done so already.
If you've migrated via a different means, then a good start would be having the database schemas for the two databases almost identical (you won't get an exact match because SQL Server 2000 has a couple of different data types to MS Access....this would've been sorted out if you used the Upsizing Wizard, though).
Do you have any pre-defined queries that you use in your Access DB or is it all just dynamic queries submitted to the database If it's just dynamic queries then you can still use these against SQL Server but I would recommend you look at encapsulating your database code in stored procedures.
A lot of your code can probably be salvaged. I'm guessing that you're currently using classes in the System.Data.OleDb namespace If you're working with SQL Server then consider switching to the System.Data.SqlClient namespace....Don't worry too much though because although the names are different, the classes in the two namespaces have essentially the same behaviour - so, for example, if your code currently uses System.Data.OleDb.OleDbConnection you can switch this to System.Data.SqlClient.SqlConnection with no other change than the class name.
Also, if anywhere in your code you're using classes in the System.Data.OleDb.OleDbType namespace you should really consider switching to the equivalent classes in the System.Data.SqlDbType namespaces.
Your connection string will obviously be different. I saw another post from spotty with a reference to a good website: www.connectionstrings.com where you can find the appropriate connection string for your SQL Server connection.
Hope that helps a little bit, but sorry if it doesn't
Satish Dadhwal
this is my connection string. Actually i do not know how to write it exactly.
aConnection =
New SqlConnection _("Server=\\AGMB6PIMO1668;Database=C:\Program Files\Microsoft SQL Server\MSSQL\Data\master.mdf;Integrated Security=SSPI")
sivakumarhtsl
OleDbConnection would become SqlConnection
OleDbCommand would become SqlCommand
OleDbParameter would become SqlParameter
I'm not aware of any classes that don't conform to the "replace OleDb with Sql" rule as I've shown above.
Also, as I said before, the classes will be essentially the same and do the same things except for the names.
Remember to add an "Imports System.Data.SqlClient" in your VB code so that you can use class names only and not have to spell out the full name including the namespace.
Hope that helps a bit
cuhkwilliam
thanks a lot.
yap.. i have create a new schema in sql server which is almost similar to my database in access. So, is it that i change all the OLEDB class to sqlClient will do.. n some of it's property...
ChrisWade
may i know what is the possible error in my codes. i have change my coding already. however, when i run my program, an error message appear saying :
System.Data.SqlClient.SqlException: SQL Server does not exist or access denied.
jazjef
thanks a lot friend! it really helps a lot..
i have another question.. i'm sorry bcoz i'm quite new with vb.net and sql server.
for the provider part(highlighted), what should i put
aConnection =
New SqlConnection ("Provider=Microsoft.Jet.OLEDB.4.0; " & _"Data Source=FAQs.mdb")
Pyit Phyo Aung
Could you please post the connection string you are using This'll help in determining what the problem is.
Without seeing the connection string, it's really just a stab in the dark. However, possible reasons could be:
* In the connection string, the name of the server isn't correct. If you are working with a default instance of SQL Server then the server name will simply be the name of the computer on which SQL Server is installed.
If you are working with a named instance, then the server name will be <Computer Name>\<Instance Name>
* If you are connecting using Windows Authentication, then you have to check that the Windows account you are using is defined as a valid login on the SQL Server and also that it has access to the databases that you will be using.
If you are connecting using SQL Server Authentication, you should check that the user name and password you are using are defined in SQL Server and that the user you are connecting as also has access to the databases you will be using.
We'll know more when we see the connection string, though
Belzicool
thankz.. it helps.. however, now new error message occur.
System.Data.SqlClient.SqlException: Cannot open database requested in login "master.mdf". Login fails. Login failed for user 'AVAGOTECH\lichioo'.
how can i check for the database access permission
fishpond777
The connections string for SQL Server databases.
Where Datasource is the name of your SQL Server and Initial Catalog is your database name on the server.
The SQL Server objects dont require a provider as they are specific to SQL Server.
So
you'll have a line like the following for a database called MyDatabase which is residing on a SQL 2000 Server called MYSQLSERVER which is using integrated security.
Dim aConnection as New SqlConnection ("Server=MySQLServer;Database=MyDatabase;Integrated Security=SSPI")