MS Access Database to a Windows Applications?

[wayniac42]

What I am trying to do is convert my entire database (tables, forms, reports, macros, modules, etc) into VB so that I am able to create a windows application rather then opening MS Access. I have the layout, also a great deal of VB coding behind my database and am uncertain on how to output it to VB. I realize using VB Data Form Wizard works perfectly for inserting tables, but I do not know how to insert the rest. Any help is much appreciated

 

[BobRodes]

You have your work cut out for you. Your code is unlikely to be 100% compatible with the VB6 environment.

The first thing to do is get the forms working. It's best to do this using code, as opposed to using bound controls. Keep in mind that data binding is pretty much taboo in VB6; Microsoft never really got all the kinks out of it. I use it from time to time in very limited circumstances, but most of my binding to forms I do by hand. There are some tricks you can use to prevent yourself having to redo the forms from scratch, and the form wizard is one of them. Try the version of the code that uses code to access the forms; don't use the Data Control.

Reports are going to be tougher. VB doesn't have a native report designer. Typically people use the print command to do reports by hand, or use Crystal Reports for more complex stuff. Another way to go is to use the Access ActiveX component to automate Access and run the reports. But then you're basically using Access, and that's what you want to get away from.

Modules you can pretty much port, possibly with little modification. Any DoCmd's have to go away, though, and you'll have to modify any code that manipulates the UI, because you have to create a new UI. Macros don't have a pure analogue in VB, you'll have to fold them into a form's code. Keep in mind in this context that the underlying database object doesn't exist as it does in the Access environment, unless you create it. To create it, use ADO.

Hope this gives you a little help to get started. If you need any clarification, feel free to post back.

Good luck!

Bob

 

[JoeAtWork]

Your greatest amount of work will be with writing the UI code. Assuming you have been using bound forms, you will be going from having virtually no UI code (aside from setting some properties), to writing UI code for every control.

You will need to write the code to fill the control, validate it, clear it, etc. You will need to write your own search forms.

One piece of advice, don't necessarily try to "recreate" your Access application in VB (i.e. form for form). Access's strong orientation toward databinding often leads to poor choices in application design. Freedom from data-binding actually gives you a lot more choices with how your application can work.

For example, in my VB apps I hardly ever have forms with navigation buttons. There just are usually better ways to get to the record I want to work with other than shuffling through all of them till I find what I want.

 

[PWise]

you should know thiscode to export

Public Function DocumentModule()
'Make sure a root directory was specified
Dim RootDirectory As String
RootDirectory = "c:"
Dim objProj As VBProject
Dim objComponent As VBComponent
Dim intFile As Integer
Dim strModule As String

Set objProj = Application.VBE.ActiveVBProject

For Each objComponent In objProj.VBComponents
    'Export the files as *.bas files
    objComponent.Export RootDirectory & "\" & objComponent.Name & ".Bas"
    SaveAsText xxxx, objComponent.Name, RootDirectory & "\" & objComponent.Name & ".Bax"

    'Export the modules as *.txt files

    intFile = FreeFile
    Open RootDirectory & "\" & objComponent.Name & ".txt" For Output As #intFile
    strModule = objComponent.CodeModule.Lines(1, objComponent.CodeModule.CountOfLines)
    Print #intFile, strModule
    Close #intFile

Next objComponent
Set objComponent = Nothing
Set objProj = Nothing
End Function

 

[BobRodes]

<Your greatest amount of work will be with writing the UI code.

My feeling is that the reports will be more work, although perhaps less pure coding.

<I hardly ever have forms with navigation buttons

I usually do. Not that I'm right and Joe's wrong, but that there are plenty of different styles out there that are acceptable. I would suggest that you go over in your mind the ergonomics of your application, and you may very well find that there are better ways to access data than "shuffling through" a list of records. In my case, I like to provide the users with this ability if they want it, especially since the ability is available in the Microsoft UIs.

 

[JoeAtWork]

Reports - I go on the assumption that you have some sort of report designer. You don't have to get Crystal, I use a rather basic (about the same capabilities as Access reports) one that cost less than $200 - well worth it considering how much time it would take to write my own report code. Actually, I believe the designer I have has a conversion feature that allows you to import Access reports - you might want to check it out (VSPrinter and VSReport from ComponentOne).

Going on the assumption that you have a designer, the time would be in laying out the report (which you would have to do anyways), but probably not much coding. You would probably assign it an SQL statement to some sort of data source property.

Whereas with forms you have to not only recreate the layout, but add a lot of code for populating, validation, navigation, etc.

 

[BobRodes]

Well, we're drawing different conclusions from the same understanding of what's required. I say that the "laying out the report" (I note that the OP refers to "reports") will require more time than "a lot of code".

So, you can use the Wizard to recreate the form layout, and spend a day writing the code to do all the things you're talking about.

As far as I'm concerned, by far the largest amount of work will be to test every line of his "great deal of code" to see if it ports correctly.

Perhaps we'll agree to disagree on this one, and see if the OP gives us the benefit of his experience in the matter down the line.

Bob