I'm trying to set up a system using the odbc drivers so that the front and back end can work over any network once the odbc has been set.

However at the moment I keep getting an error message about the odbc not having the correct parameters ( amount expected = 2) when I try to open a recordset. The other errors it generates relate to the object being closed which I realised is due to the fact that the connection is opening for some reason.

I'm using the code from chapter 12 of beginning access vba as a base for my system.

gcnconn is a global variable declared as a connection
gstrconnection is a global string variable

I've watched the variables and stepped through the code from when the form opens. Each section other than the processrecordset function seems to works fine and returns the results as expected.

The odbc is set up under system dsn as far as I can tell correctly. I seperated out the lines just to see if it was something i'd done in the writing of a single line.

The connection code is:

Sub opendbconnection()

On Error GoTo Handleerror

gstrconnection = "ODBC;DSN=compcontrol;"

'create a new connection instance and open it using the connection
'string
Set gcnconn = New ADODB.Connection
With gcnconn
    .Provider = "MSDASQL"
    .Properties("Data Source").value = gstrconnection
    .Open
End With

Exit Sub

Handleerror:
generalerrorhandler Err.Number, Err.Description, "modstartup", "opendbconnection"
    Exit Sub
End Sub

Function processrecordset(strsqlstatement As String) As ADODB.Recordset

On Error GoTo Handleerror

'open the connection to the database
Call opendbconnection

'create a new instance of a recordset
Dim rscont As New ADODB.Recordset

'set various properties of the recordset

With rscont
    'specify a cursortype and locktype that will allow updates
    .CursorType = adOpenKeyset
    .CursorLocation = adUseClient
    .LockType = adLockBatchOptimistic
    .ActiveConnection = gcnconn
    .Source = strsqlstatement
    'populate
    .Open
    'disconnect the recordset
    .ActiveConnection = Nothing
    'sort the recordset
End With

rscont.Open
'close the connection to the database
Call closedbconnection

'return the recordset
Set processrecordset = rscont

Exit Function

Handleerror:
    generalerrorhandler Err.Number, Err.Description, "moddblogic", "processrecordset"
    Exit Function

End Function


I've looked all over the web for setting up the connections within vba and within odbc and as far as I can tell its right so please help resolve this issue

Wow, that is the long way around. Anyway, where is this: strsqlstatement


The connection won't open without that SQL string.

What are you trying to do with the connection and recordset? Is this a SQL back end (it looks like it), what is "MSDASQL"?

Here is what I usually do if I am using a system dsn (which I usually don't use because then all the users have to have one created on their system... I use SQL provider statements instead since that makes the application completely portable on the network:)

Set objConn = CreateObject("ADODB.Connection")
Set objRS = CreateObject("ADODB.Recordset")
objConn.Open "DSN=DSNName;", UID, PWD
objRS.CursorLocation = 3

objRS.Open "SELECT * FROM MyTable", objConn, 3, 3

Ah, the good old days of DSN's. Now I do this:

Set cn = New ADODB.Connection
With cn
    .CursorLocation = adUseClient
End With
cn.Open "Provider=SQLOLEDB;Data Source=SQLServerName;Initial Catalog=SQLDatabaseName;UID=UID;PWD=PWD"

sSQL = "SELECT * FROM MyTable"

Set rs = New ADODB.Recordset
    rs.CursorType = adOpenDynamic
    rs.LockType = adLockOptimistic
    rs.Open sSQL, cn

Did any of that help?




mmcdonal

Its of some help.

To explain a bit. The system dsn is used because it will contain the network address of the datastore. When setting the proposed system the SDN will be set up by the network administrator or myself. Its been done this way because won't know the network setup of the end system and it seemed with my limited knowledge to be the easiest way. Its a simple front end / back end setup with all the forms and modules on the front end with the queries and tables on the back end. The queries are run through the modules on the front end.

The msdasql is apparently (access programming by example book) used to connect to a mdb access database. Not using sql as can't get msde to install and run on any of the computers I have, nor is it likely that sql server will be used in the proposed market for the system.

The sql string comes from any of a myriad of forms. I checked that it was there during the trace through before posting. Its there however it still doesn't open.

I'll repost if it works.

Sorry that didn't work, as still got the same error message.

All I'm trying to do is have a front end and a back end when I don't know where the backend is going to be stored.

I tried changing it to microsoft jet 4 and the the data source to a local database.

The connection opens fine as using a flash drive and can see the drive activate at the correct time.

so it comes back to understanding and setting the connection correctly for a system dsn and figuring out why the recordset wont open.

Everything comes back fine until it sent to open which comes back with the error number: -2147217904, parameters missing

The sql transferred from another procedure is:

SELECT * " & _
        "FROM tblcomplaints WHERE txtstatus <> 'Closed' " & _
        "ORDER BY complaintid"

You know you are missing the opening double quotes in your post. Is it that way in your code?
Try putting this in the code after your string:

MsgBox strsqlstatement

and see if this looks right. Then take your SQL string and paste it into the SQL View of the query designer and see what you get. Use it like SQL Query Analyzer. If you do not get results, then modify the string until it works, and copy and paste that string into your code. If that still doesn't work, then it is not the string, and we can move on to some other issue.


mmcdonal

Rechecking the sql string worked - there was a stupid mistake in it though not the " - under jet 4 code however when I switched back to msdasql and dsn it still didn't work. To make matters worse it now can't even see the dsn and says it doesn't exist whereas before it was actually activating the drive. The only change i've done to that routine in between has been to take out the ' infront of each line grrrrr.