Vb + Msaccess

[amolbehl]

Hi I am trying to develop a Database application with VB6 and MS ACCESS.

Sub Main()
' First we initialize paths for all needed files
gstrDBPath = "c:\dvp\PBG "
gOrderDetailsPa th = "c:\dvp\PBG\Ord erDetail.txt"
'gOrderDetailsP ath = "G:\order.t xt"

'Load frmImportData
'frmImportData. Show
Set gdbFML = OpenDatabase(gs trDBPath & "\WVCommon.mdb" )
frmImportData.S how

Dim frm As Form
' Loop through all open forms and close them
For Each frm In Forms
Unload frm
Next frm
End Sub

This is the code in my "module".

Now when I uncomment
'Load frmImportData
I get a Runtime error 91

at the statement in the form where I am using the following line

Set rs = gdbFML.OpenReco rdset(strSQL)

I went thru the forms here and realized I may not have added the needed reference but the only problem is I dont know which reference is needed.

Could somene plz help me out with this plz.

[Heggr]

Hi I am trying to develop a Database application with VB6 and MS ACCESS.

Sub Main()
' First we initialize paths for all needed files
gstrDBPath = "c:\dvp\PBG "
gOrderDetailsPa th = "c:\dvp\PBG\Ord erDetail.txt"
'gOrderDetailsP ath = "G:\order.t xt"

'Load frmImportData
'frmImportData. Show
Set gdbFML = OpenDatabase(gs trDBPath & "\WVCommon.mdb" )
frmImportData.S how

Dim frm As Form
' Loop through all open forms and close them
For Each frm In Forms
Unload frm
Next frm
End Sub

This is the code in my "module".

Now when I uncomment
'Load frmImportData
I get a Runtime error 91

at the statement in the form where I am using the following line

Set rs = gdbFML.OpenReco rdset(strSQL)

I went thru the forms here and realized I may not have added the needed reference but the only problem is I dont know which reference is needed.

Could somene plz help me out with this plz.

When I do this I set a TYPE in my OpenRecordset statement. Try something like this: Set rs = gdbFML.OpenReco rdset(strSQL, dbOpenDynaset)

Heggr

[Heggr]

I left out the reference you were looking for also. You will want to add a reference to Microsoft DAO 3.6 Object Library. Your version may be different than 3.6 but everything else should say the same.

[amolbehl]

I left out the reference you were looking for also. You will want to add a reference to Microsoft DAO 3.6 Object Library. Your version may be different than 3.6 but everything else should say the same.

Hey thanx a lot .... I have added the DAO Library, but still get the same error when I try to load the form. Please tell me wht I am doing wrong.

[Heggr]

If the error is happening on the same line still I would make sure you have declared the rs variable as DAO.Recordset. You may also need to declare gdbFML as a DAO.Database. I find it extremely helpful to use Option Explicit in my code to force me to declare all my variables.

If the error is happening else where in the form I would need additional information to be able to offer any assistance.

[amolbehl]

Man none of those things helped check the code


Sub Load_Tables(tab name As String, filename As String)
Dim response As Integer
Dim temp As Integer

'First get the total number of record in OrderDetail
strSQL = "SELECT Count(*) AS totalordercount FROM OrderDetail;"
Set rs = gdbFML.OpenReco rdset(strSQL, dbOpenDynaset)

temp = rs.RecordCount
'MsgBox temp

' If there are records then we ask if we need to overwrite them
If Not rs.EOF Then
totalOrderCount = rs!totalOrderCo unt
End If

If totalOrderCount <> 0 Then
response = MsgBox("The Table has records, Do you want to delete them", vbInformation + vbYesNo, "Confirmati on")
If response = vbYes Then
delete_data ("OrderDetai l")
source_data ("OrderDetai l")
Else
MsgBox ("The table might have duplicate entries hence the sourcing aborted")
End If
Else
'else there no Orders Found .. just write to table
MsgBox ("No Records Found .... Safely sourcing data")
source_data ("OrderDetai l")
End If
End Sub



And my MODULE has the following code

Global gdbFML As DAO.Database
Global rs As DAO.Recordset
Global gstrDBPath As String
Global gOrderDetailsPa th As String

Sub Main()
' First we initialize paths for all needed files
gstrDBPath = "c:\dvp\PBG "
gOrderDetailsPa th = "c:\XYZ\OrderDe tail.txt"

'MsgBox "x"
'Load frmImportData
'frmImportData. Show vbModal
Set gdbFML = OpenDatabase(gs trDBPath & "\WVCommon.mdb" )
frmImportData.S how
End Sub


Now this project has just 1 form which has that code.

Now the project starts from the main() in the module

When i run the project I get

runtime error 91

and the when I hot debug, it comes to line

"Set rs = gdbFML.OpenReco rdset(strSQL, dbOpenDynaset)"

and stops.

[Heggr]

In your original post you stated
Now when I uncomment
'Load frmImportData
I get a Runtime error 91

If you are still uncommenting this line and trying to run the code then your problem is that you are setting your gdbFML variable after you are loading the form. If you move the Set gdbFML line to before the Load frmImportData you should be OK.

If you are leaving the Load frmImportData commented out then I am not seeing why you are having the problem. Setting gdbFML to the database before showing the form is the correct way to do it. By doing a frmImportData.S how should both load and show the form on the screen.

[amolbehl]

Thanx a lot ....
The error is gone

But the form is not getting displayed on the screen can u think of any reason why tis is happening????

[amolbehl]

Thanx a lot for all ur help ... but I kinda solved the problem

[antony123]

The problem is at the sql statement.Drive r donot allow diredt sql quries like this .Remove Count(*).The code will work