Migration from MS-Access to MSSQL 2005 Server using VB 6

[mynameiskamath]

Hi,
I am currently migrating an database application from MS-Access to MSSQL Server 2005.

Details of MS-Access application
Certain VB Forms has been designed within the MS-Access database. They wil be talking to some tables in the MS-Access DB and make necessary updates.

What is that I have done so far?

• I have now migrated all the data present inside MS-Access(Tables + data) to MSSQL 2005 Server.
• Created a VB6 application and designed the similar forums.
• I have made the Database connection using DataEnvironment variable and checked the connectivity to MSSQL 2005 Server DB. The Connection is fine. I am able to connect from VB6 to MSSQL 2005 Server.
• I copy pasted the entire piece of code from each form in MS-Access to the form in MSSQL 2005 Server. I guess this is NOT THE RIGHT WAY to do it. Not sure
• I tried to EXECUTE the VB6 application and getting the error in the line in the 1st form that would load
• Private Sub Form_Load()
  Application.CommandBars("Menu Bar").Enabled = False ------

In MS-Access when I type in Application. the set of Options which can be used are displayed. But when I type Application. in VB6 nothing happens. VB6 is NOT ABLE to RECOGNISE this Application.

I want the EQUIVALENT of Application. in VB6

Since I haven't worked on VB before, I am not very sure how to proceed further. Can somebody HELP ME here?
WHAT TO DO NEXT ?

Regards
Kamath

 

[HiTechCoach]

copy pasted the entire piece of code from each form in MS-Access to the form ...

I would not recommend this at all.

Since VB6 works totally different that Access/VBA, you can not use any of the forms or reports. Some VBA code that is not Application (Access) specific can be ported over. That usually mean you can copy code modules and then modify the code to work.

Because VB6 works totally different than Access, I would recommend that you start from scratch and do everything the VB6 way. You will need to learn how VB6 works different (there are lots of things.

Note: You can use an Access front end with SQL Server.

 

[mynameiskamath]

Hi HiTechCoach
Thanks for the quick response. I am Not very sure, what you mean by "Note: You can use an Access front end with SQL Server"

Current Architecture

1. My_test_project.mdb is the MDB file

• This MDB file has 4 FORMS embedded in it. On execution, it would connect to some of the tables embedded in the same MDB file and fetches the required data and display the same.

Do you mean to say that, I can use these Forms and connect directly to MSSQL 2005 Server rather than to MS-Access database?

If YES, then can I achieve this by going to Databasetools tab and clicking on SQL Server and proceed further? or you have better methods to achieve this?

Regards
Kamath

 

[HiTechCoach]

Do you mean to say that, I can use these Forms and connect directly to MSSQL 2005 Server rather than to MS-Access database?

That is correct!

I use Access as the front end (forms/reports, etc) for SQl server with great success. I even use Acess as a report write over Crystal with SQL servers.

See:
Beginning SQL Server Development

Hope this helps ...

 

[mynameiskamath]

Thanks Mr.HightecCoach,

I just happened to use the below mentioned option and Linked some tables to achieve this after lot of R&D

So far I have just tested for 2 tables and they got updated directly in SQL Server tables using MS-Access forms

I had tested this out on one of the Microsoft DB that I had downloaded from the internet. The MS-Access DB that I am using is throwing an ERROR when i click on SQL Server in the Databasetools tab whereas the sample Microsoft DB didn't throw any error.

I will just try to figure it out & resolve it and get back to you if I have any issues.

Thanks a tonn once again

Regards
Kamath