ado recordset in visual basic database access

[weiwei]

Hi:
I am trying to access my access database by using visual basic 6.0. I
want to use recordset method to get data from query.
I can get connection established, but I get error in further debugging
with message "arguments are of the wrong type, are
out of the acceptable range, are in conflict with one another". Anyone
can help me out, Below is my current code

Option Explicit
Sub Main()
Call Upload_User_Attribute
End Sub

Public Function Upload_User_Attribute()
Dim conn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rs As ADODB.Recordset
Dim sConnString As String
Dim iCtr As Integer
Dim PersonnelNumber, LastSSN, FirstName, LastName
Dim strPN, strLastSSN, strFirstName, strLastName
Dim ConnectionString, ActiveConnection, CursorLocation, adUseClient,
strSQL, adOpenFowardOnly, adLockReadOnly, fnSQL_RS

sConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\Deploy08022005\data\user_attribute.mdb"

Set conn = CreateObject("ADODB.Connection")
conn.ConnectionString = sConnString
conn.Open
Set rs = CreateObject("ADODB.Recordset")
rs.ActiveConnection = conn
'rs.CursorLocation = adUseClient
strSQL = "SELECT PersonnelNumber, LastSSN, FirstName, LastName FROM
Sap_flat"
'rs.Source = strSQL
rs.Open strSQL, , adOpenForwardOnly, adLockReadOnly

If Not rs.EOF Then
Do While Not rs.EOF

strPN = rs("PersonnnelNumber")
strLastSSN = rs.Fields("LastSSN")
strFirstName = rs.Fields("FirstName")
strLastName = rs.Fields("LastName")

rs.MoveNext
Loop
MsgBox "No records found"
End If

Set fnSQL_RS = rs
rs.ActiveConnection = Nothing
conn.Close
Set conn = Nothing

End Function

[Veign]

Try this (air-code):

'----------------------Start Code-----------------------
Public Function Upload_User_Attribute()

Dim conn As ADODB.Connection

Dim rs As ADODB.Recordset
Dim sConnString As String

Dim strPN As String
Dim strLastSSN As String, strFirstName As String, strLastName As tring
Dim strSQL As String

'Create the connection

Set conn = CreateObject("ADODB.Connection")

sConnString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _

"Data Source=C:\Deploy08022005\data\user_attribute.mdb"

conn.Open sConnString

'Create the Recordset

Set rs = CreateObject("ADODB.Recordset")

strSQL = "SELECT PersonnelNumber, LastSSN, FirstName, LastName FROM
Sap_flat"

rs.Open strSQL, conn, adOpenForwardOnly, adLockReadOnly

'Check for records
If Not rs.EOF Then
'Loop through all records
Do Until rs.EOF
strPN = rs("PersonnnelNumber").Value & ""
strLastSSN = rs.Fields("LastSSN").Value & ""
strFirstName = rs.Fields("FirstName").Value & ""
strLastName = rs.Fields("LastName").Value & ""

rs.MoveNext
Loop
MsgBox "No records found"
End If

'Cleanup
If rs.state = adStateOpen Then rs.Close
Set rs = Nothing
If conn.state = adStateOpen Then conn.Close
Set conn = Nothing

End Function
'-----------------------End Code----------------------------

--
Chris Hanscom - Microsoft MVP (VB)
Veign's Resource Center
http://www.veign.com/vrc_main.asp
--

"weiwei" <weihu...@gmail.com> wrote in message
news:1124223920.5...@z14g2000cwz.googlegroups.com...

[weiwei]

Thanks for your time,
I used your code, however, It still has bug, so far, I can debug all
the way down to --strPN = rs("PersonnnelNumber").Value & ""
Then it raise error again, saying "run time error 3265" Item cannot be
found in the collection corresponding to the requested name or ordinal

do you have any idea,
thanks in advance

[weiwei]

Try this (air-code):

'----------------------Start Code-----------------------
Public Function Upload_User_Attribute()

Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sConnString As String
Dim strPN As String
Dim strLastSSN As String, strFirstName As String, strLastName As tring
Dim strSQL As String

'Create the connection
Set conn = CreateObject("ADODB.Connection­")

sConnString = "Provider=Microsoft.Jet.OLEDB.­4.0;" & _

"Data Source=C:\Deploy08022005\data\­user_attribute.mdb"
conn.Open sConnString

'Create the Recordset
Set rs = CreateObject("ADODB.Recordset"­)
strSQL = "SELECT PersonnelNumber, LastSSN, FirstName, LastName FROM
Sap_flat"
rs.Open strSQL, conn, adOpenForwardOnly, adLockReadOnly

'Check for records
If Not rs.EOF Then
'Loop through all records
Do Until rs.EOF

strPN = rs("PersonnnelNumber").Value & ""

strLastSSN = rs.Fields("LastSSN").Value & ""
strFirstName = rs.Fields("FirstName").Value & ""
strLastName = rs.Fields("LastName").Value & ""

rs.MoveNext
Loop
MsgBox "No records found"
End If

'Cleanup
If rs.state = adStateOpen Then rs.Close
Set rs = Nothing
If conn.state = adStateOpen Then conn.Close
Set conn = Nothing

End Function
'-----------------------End Code--------------------------­--

--
Chris Hanscom - Microsoft MVP (VB)
Veign's Resource Center
http://www.veign.com/vrc_main.asp
--

"weiwei" <weihuang...@gmail.com> wrote in message

news:1124223920.5...@z14g2000cwz.googlegroups.com...

- Hide quoted text -
- Show quoted text -

> Hi:
> I am trying to access my access database by using visual basic 6.0. I
> want to use recordset method to get data from query.
> I can get connection established, but I get error in further debugging
> with message "arguments are of the wrong type, are
> out of the acceptable range, are in conflict with one another". Anyone
> can help me out, Below is my current code

> Option Explicit
> Sub Main()
> Call Upload_User_Attribute
> End Sub

> Public Function Upload_User_Attribute()
> Dim conn As New ADODB.Connection
> Dim cmd As New ADODB.Command
> Dim rs As ADODB.Recordset
> Dim sConnString As String
> Dim iCtr As Integer
> Dim PersonnelNumber, LastSSN, FirstName, LastName
> Dim strPN, strLastSSN, strFirstName, strLastName
> Dim ConnectionString, ActiveConnection, CursorLocation, adUseClient,
> strSQL, adOpenFowardOnly, adLockReadOnly, fnSQL_RS

> sConnString = "Provider=Microsoft.Jet.OLEDB.­4.0;Data

[Eric Schuyler]

I haven't seen the earlier posts in this thread, but it looks like the
error is due to misspelling "PersonnelNumber" (2 n's) as
"PersonnnelNumber" (3 n's). Correct the spelling and try again!

Regards,
Eric

"weiwei" <weihu...@gmail.com> wrote in news:1124228073.823059.137580
@g49g2000cwa.googlegroups.com:

[Veign]

Check for spelling errors:

strPN = rs("PersonnnelNumber").Value & ""

Should it be:
strPN = rs("PersonnelNumber").Value & ""

I just copied your original code and didn't change spellings...

--
Chris Hanscom - Microsoft MVP (VB)
Veign's Resource Center
http://www.veign.com/vrc_main.asp
--

"weiwei" <weihu...@gmail.com> wrote in message

news:1124227975.8...@o13g2000cwo.googlegroups.com...

[weiwei]

Thank you very much,
yes, it was my mistake with spelling error, so far it is working
I really appreciated it.