VB program connecting to Access DB - arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.

lcha
lcha used Ask the Experts™
on
Hello experts,

I am learning how to write a program in Visual Studio 2008, using visual basic .net and ADO (referencing Microsoft Active X Data Objects 2.8 Library).

Using the code below, it fails at the line where I set a recordset's .source value to a sql string (see code below)

The error I'm getting is " arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.

Pls advise if you can see the issue here.   Thanks in advance!
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim strSQL As String

cn.Open("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Documents and Settings\Administrator\My Documents\testdb.accdb")

strSQL = "SELECT * FROM table1;"

                  With rs
                        .ActiveConnection = cn
                        .Source = strSQL
                        .Open()
                    End With
                    rs.Open()

                    If rs.EOF = False Then
                        rs.MoveFirst()
                    End If
                    Do
                        MsgBox(rs.Fields("Field1"))
                        rs.MoveNext()

                    Loop Until rs.EOF = True

                    cn.Close()
                    cn = Nothing
                    rs = Nothing

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Shouldn't you put .mdb on this line of code? Where you have this:

cn.Open("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Documents and Settings\Administrator\My Documents\testdb.accdb")

You should have this:

cn.Open("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Documents and Settings\Administrator\My Documents\testdb.mdb")

jppinto
ǩa̹̼͍̓̂ͪͤͭ̓u͈̳̟͕̬ͩ͂̌͌̾̀ͪf̭̤͉̅̋͛͂̓͛̈m̩̘̱̃e͙̳͊̑̂ͦ̌ͯ̚d͋̋ͧ̑ͯ͛̉Glanced up at my screen and thought I had coded the Matrix...  Turns out, I just fell asleep on the keyboard.
Most Valuable Expert 2011
Top Expert 2015

Commented:
Is there a reason why you are calling Open() twice on the RecordSet? Once within the "With" and once just after it.

Author

Commented:
You can please disregard the calling of open twice ... it's not like that in the current code.  

Also, I am using Access 2007, so the database extension is correct ... .accdb

thanks
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
raising points on this

Author

Commented:
great, appreciate this.   I'll continue working on this tommorrow and keep you posted.   thanks.

Author

Commented:
thank you!   I got it working now!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial