Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Serbia and Montenegro (Yugoslavia)
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Too Few Parameters connecting to Access (VB6 / Access 97)

    I have developed an application in VB6 using and Access97 back end. I need to put user level security on the app before distributing it to the end users. I've put the user level security on the back end and am now trying to access it from the VB front end. There is plenty of data in the KB on connection strings, but very little in how to use it to access an Access Workgroup Information File.

    I found a class module in a a tool called Total Visual Sourcebook that was able to access the data, but the help on the code was poor and my knowledge level isn't at the point that I could figure out how I could grab a connection object out of it.

    I did step through the code and determined that it is simply passing the path to the Workgroup information file as a parameter of the connection string called SystemDB. I've also tried setting that using the DBEngine object.

    With the below code, I was able to successfully create a connection object, however, no matter what I do I get an error: "Too few parameters expected" when I try to do the rs.Open , cn. This error also occurs when I fill in the parameters completely (and comment out where I filled in those properties in the With statement). I also get the same error if I use the Execute method of the connection object.

    I don't think I've ever been so stumpped, I've tried everything I can think of. I'd appreciate any input.

    I don't want to post it due to it being copywrited but I've commented out in the top section of the code is the connect string produced by the class module.

    Code is as follows:


    Public Function GetRS(sSource As String) As ADODB.Recordset


    'Connect String: Provider=MSDASQL.1;Extended Properties="DBQ=CatabaseOncology97.mdb;Driver={Microsoft Access Driver (*.mdb)};DriverId=281;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;PWD=cns;Sy stemDB=CATABASEROADMAP.MDW;UID=mshea;"

    Dim rs As ADODB.Recordset
    '
    ''Dim cn As New ADODB.Connection
    '
    Set rs = New ADODB.Recordset

    'sConnString = "Provider=Microsoft.jet.oledb.4.0;Driver={Microsof t Access Driver (*.mdb)};" _
    ' & "Data Source=CatabaseOncology97.mdb;UID=mshea;PWD=cns;SystemDB=CATABASEROADMAP.MDW"

    sConnString = "Provider=MSDASQL.1;DBQ=CatabaseOncology97.mdb;Driver={Microsoft Access Driver (*.mdb)};DriverId=281;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;PWD=viking ;SystemDB=CATABASEROADMAP.MDW;UID=mshea;"

    cn.Open sConnString

    rs.Source = sSource
    rs.CursorLocation = adUseClient
    rs.CursorType = adOpenStatic
    rs.LockType = adLockBatchOptimistic

    rs.Open , cn

    Set GetRS = rs

    Set rs.ActiveConnection = Nothing

    cn.Close

    'If cn.Errors.Count > 0 Then
    ' MsgBox Err.Number
    'Else
    ' MsgBox "Error #" & Err.Number & " " & Err.Description
    'End If
    '
    '

    End Function

  2. #2
    Star Lounger
    Join Date
    Nov 2001
    Location
    Illinois, USA
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Too Few Parameters connecting to Access (VB6 / Access 97)

    Using the visual data tools in Visual Basic is a great way to get someone else (namely, VB!) to build the connection string for you. Go to the Data View Window, Add a Data Link. Then just fill in the Provider and Connection dialog pages, click on the "All" tab, locate the name JET OLE DB: System Database in the list, edit the value to point to the mdw file. Now save that connection and when you right click it in the Data View Window and choose Properties you can see the string created. It will have tons of extra properties too including many that are the defaults. If you want just the "bare bones" properties, try a similar trick using an ADO control instead and just look at its connection string properties after setting it up.

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Serbia and Montenegro (Yugoslavia)
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Too Few Parameters connecting to Access (VB6 / Access 97)

    Hi Dottie,

    I never used that before, it looks like it has potential.

    Okay, I tried the first part of your suggestion. At first I had trouble creating the connection, like when I clicked on Test Connection before saving it, it would succeed. But then when I saved it and tried to access the data it would return an error. When I went into modify the "Allow blank password" box was re-checked and my password I put in was blanked. I resolved this by clicking on the Save Password check box in the designer wizard (if that's what it's called).

    I found that the string did not contain the Password= in it, probably having something to do with the save password option I selected. The connection error stated not a valid account or password. When I added it in manually, it returned my old friend the error message "No value given for one or more required parameters" when I did an rs.Open , cn.

    Actually it sounds like the connection succeeded and the recordset is having trouble opening. But I've opened recordsets before like this with no problem, I can't think of what other "Parameters" this thing would need.

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Serbia and Montenegro (Yugoslavia)
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Too Few Parameters connecting to Access (VB6 / Access 97)

    Dottie,

    In my troubleshooting I tried changing the SQL text that I'm passing in to simply SELECT * FROM tblPath and it works. Before I had SELECT tblPath.Seq, tblPath.CreateDate, tblPath.Path FROM tblPath WHERE tblPath.MRNumber = 123 ORDER BY tblPath.SEQ.

    I'm still trying to figure out why it isn't working but I've at least narrowed it down to this. I wanted to let you know so I don't have you chasing the same wild goose I've been, and to disregard my last message.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •