Accessing database through vb(Urgent Please)

[anukraju07]

Hi,

I want to access a MS access database table with VB 6. One of the field in db is date and the type is date/time.I am feeding the date value in a vb form in a datepicker and want to display the records in a data report. When I am running the programme an error of "Invalid Data Source" appears.Both my database and programs are in the same directory. Where should I look for to clear the problem? Please help .

[VBWheaties]

Hi,

I want to access a MS access database table with VB 6. One of the field in db is date and the type is date/time.I am feeding the date value in a vb form in a datepicker and want to display the records in a data report. When I am running the programme an error of "Invalid Data Source" appears.Both my database and programs are in the same directory. Where should I look for to clear the problem? Please help .

What are you using to connect to Access? DAO, ADO, ADODC? Need more detail. Wouldnt hurt to post your code, too.

[anukraju07]

What are you using to connect to Access? DAO, ADO, ADODC? Need more detail. Wouldnt hurt to post your code, too.

Hi,
[code=vb]
Dim db3 As ADODB.Connectio n
Dim rs3 As ADODB.Recordset
Private Sub Command1_Click( )
Dim x
Dim s2, s3
s2 = DTPicker1.Day & "/" & DTPicker1.Month & "/" & DTPicker1.Year
s3 = Combo1.Text
MsgBox s2

DataEnvironment 2.Command1 (s2), (s3)

DataReport2.Sho w
Form1.Show

End Sub

Private Sub Command2_Click( )
Form1.Show

End Sub

Private Sub Form_Load()
Dim query, x


Set db3 = New ADODB.Connectio n
Set rs3 = New ADODB.Recordset

db3.Open "Provider=Micro soft.Jet.OLEDB. 4.0;Data Source=" & App.Path & "\hrme.mdb" & ";Persist Security Info=False"

rs3.Open "select * from attendence ", db3, adOpenKeyset, adLockOptimisti c
DTPicker1.Value = DateValue(Now)
x = DTPicker1.Value

DTPicker1.Value = DateValue(Now)

Combo1.Text = "A"

End Sub[/code]
Hope this can help me

[debasisdas]

Run in debug mode using break point and check which line is creating the error.

[VBWheaties]

Hi,
[code=vb]
Dim db3 As ADODB.Connectio n
Dim rs3 As ADODB.Recordset
Private Sub Command1_Click( )
Dim x
Dim s2, s3
s2 = DTPicker1.Day & "/" & DTPicker1.Month & "/" & DTPicker1.Year
s3 = Combo1.Text
MsgBox s2

DataEnvironment 2.Command1 (s2), (s3)

DataReport2.Sho w
Form1.Show

End Sub

Private Sub Command2_Click( )
Form1.Show

End Sub

Private Sub Form_Load()
Dim query, x


Set db3 = New ADODB.Connectio n
Set rs3 = New ADODB.Recordset

db3.Open "Provider=Micro soft.Jet.OLEDB. 4.0;Data Source=" & App.Path & "\hrme.mdb" & ";Persist Security Info=False"

rs3.Open "select * from attendence ", db3, adOpenKeyset, adLockOptimisti c
DTPicker1.Value = DateValue(Now)
x = DTPicker1.Value

DTPicker1.Value = DateValue(Now)

Combo1.Text = "A"

End Sub[/code]
Hope this can help me

From what I can tell, you have no runtime connection initialization for your dataEnvironment . Just make sure the DataEnvironment is connected to its DataSource for each connection or command object associated with it.

[anukraju07]

From what I can tell, you have no runtime connection initialization for your dataEnvironment . Just make sure the DataEnvironment is connected to its DataSource for each connection or command object associated with it.

Hi,

I tried the following to check the connection as u suggested.

Designers--> dataenviornment 2 -> command1;
(rightclick) and selected design and run the query(again right click),

Select * from attendence where date=? and shift=?

in the define query parameter window , fed for first ? as date value(7/3/2008)
and for second ? as A
then pressed OK.

I am getting the records displayed in the bottom.

But when I'm running the programme by selecting Command button, I'm getting an error "Invalid Data Source".
Right now I am stuck up and need help

[VBWheaties]

In VB6, the design-time is not really the run-time.
In the run-time, you must always make sure your connection is working.
When your application runs, it is probably not connected to the datasource by default.

This is something you will need to make sure in code.
How to do this?

As you probably know, your DataEnvironment is a global object that you can access programatically :

   DataEnvironment1.<property or method>

The <property or method> part will depend on the connection objects and how you named them. In other words, if you named one Apple, it would be DataEnvironment 1.Apple

From there, it is just a matter of using the correct ADO methods to make sure the connection is initialized. I assume you are knowledgeable of ADO. If not, let me know (I dont want to assume you are not).

If that doesnt do it, try casting your command parameters to their proper types.
For example, I see you have a Date field. If that is DateTime, you should pass it to the command as

   cDate(DateString)

The same wouldnt hurt for a string type:

   cStr(FieldString)

Hope that helps

[anukraju07]

In VB6, the design-time is not really the run-time.
In the run-time, you must always make sure your connection is working.
When your application runs, it is probably not connected to the datasource by default.

This is something you will need to make sure in code.
How to do this?

As you probably know, your DataEnvironment is a global object that you can access programatically :

[CODE=vb]
DataEnvironment 1.<property or method>
[/CODE]

The <property or method> part will depend on the connection objects and how you named them. In other words, if you named one Apple, it would be DataEnvironment 1.Apple

From there, it is just a matter of using the correct ADO methods to make sure the connection is initialized. I assume you are knowledgeable of ADO. If not, let me know (I dont want to assume you are not).

If that doesnt do it, try casting your command parameters to their proper types.
For example, I see you have a Date field. If that is DateTime, you should pass it to the command as

[CODE=vb]
cDate(DateStrin g)
[/CODE]

The same wouldnt hurt for a string type:
[CODE=vb]
cStr(FieldStrin g)
[/CODE]

Hope that helps

9th March'08

Hi,

This is me again to find a solution for my connecting to Access through VB6.0

I tried the following as advised by u

1. Set system DSN to ‘hrme’ driver do Microsoft Access (*.mdb) (my database name) through control panel route
2. In the form where I input my variables (date through dtpicker and shift through combo box), added a ADODC .
3. Set the properties by building a connection string Provider=Micros oft.Jet.OLEDB.4 .0;Data Source=C:\Elogg ing1\hrme.mdb;P ersist Security Info=False ( test connection went successful)

4. In Recordsource property ; command type as ‘adcmd table’ and table name as ‘attendence’ (the table I am using)

5. Run the code as mentioned earlier (code is reproduced for your convenience )

(At error level I got a blank message box .)

and result as “Invalid Data Source”

My VB programe and database are in the same directory C:\Elogging1

I tried to access the db through ‘Data Form Wizard’ also

Then when I select database format as Acess and database name as C:\Elogging1\hr me.mdb; I am getting an error saying “ Unrecognised database format C:\Elogging1\hr me.mdb”. But surprisingly I am able to open a table called hrm in the same db in another form without any problem.

Please help me and advise anything I missed in this procedure.

Sorry for taking much of your valuable time and thanking u for ur support


[code=vb]

Dim db3 As ADODB.Connectio n
Dim rs3 As ADODB.Recordset


Private Sub Command1_Click( )
Dim x

Dim s2, s3

s2 = DTPicker1.Day & "/" & DTPicker1.Month & "/" & DTPicker1.Year
s3 = Combo1.Text

MsgBox s2

DataEnvironment 1.Command2 (s2), (s3)

DataReport2.Sho w

Form1.Show

End Sub

Private Sub Command2_Click( )
Form1.Show

End Sub

Private Sub Form_Load()
Dim query, x

On Error GoTo ErrLabel

Set db3 = New ADODB.Connectio n
Set rs3 = New ADODB.Recordset


db3.Open "Provider=Micro soft.Jet.OLEDB. 4.0;Data Source=C:\Elogg ing1\hrme.mdb;P ersist Security Info=False"



rs3.Open "SELECT * FROM attendence", db3, adOpenStatic, adLockOptimisti c


ErrLabel:
MsgBox Err.Description

DTPicker1.Value = DateValue(Now)

Combo1.Text = "A"

End Sub[/code]

[VBWheaties]

'Unrecognized database format' is usually because you are using Access 97-era technology with a later version of Access. DAO will not work with Access 2007 or (I think) XP.
However, the message could also mean the database is corrupt. In that case, open the Access database (if you can) and do a Compact/Repair through Tools>Database Utilities>Compa ct Database and Repair Database

If repair/compact doesnt solve the issue, read on....

I didn't see where you are making sure the DataReports DataEnvironment is initialized properly. I found this helpful article which demonstrates how to rebind your dataenvironment which is what you need to do because you keep getting Invalid Data Source:
http://www.xtremecomp. com/tips/xttip14.htm