Opening recordsets with new back end

[303factory]

I've recently attempted to migrate from an Access back end to a Microsoft SQL Server Express 2007 back end. I've linked up the tables to my Access front end client no problem, and anything that gets data using an Access query works fine. However when I try and open a recordset using code I get an error

'ODBC--connection to 'MIDSQL' failed' (where MIDSQL is the name of my ODBC connection)

I'm using the following code, which all works fine with my old Access back end.
(connection declaration)

Set gMIDdbase = New ADODB.Connection
    With gMIDdbase
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .ConnectionString = CurrentProject.Connection
        .Open
    End With

Code causing debug:

Dim rstRS As ADODB.Recordset            
strSQL = "SELECT * FROM tblExhibitInfo WHERE JobRef =" & Chr(34) & Me.JobRef & Chr(34) & " AND Exhibit =" & Chr(34) & Me.Exhibit & Chr(34)
 Set rstRS = New ADODB.Recordset
rstRS.Open strSQL, gMIDdbase, adOpenKeyset, adLockOptimistic, adCmdText

Anyone have any idea why I'm getting this problem? Is my code no longer valid with a SQL server back end?

Thanks

303

 

[boblarson]

First you say

migrate from an Access back end to a Microsoft SQL Server Express 2007 back end

But there is no SQL Server Express 2007. There is 2005 and there is 2008, but no 2007. So, is it 2005 or 2008?

Second, you are using a connection string to an Access database not a SQL Server Database, so you are going to have to change that.

Go to http://www.connectionstrings.com to get the correct one for your version of SQL Server Express.

 

[303factory]

First you say

But there is no SQL Server Express 2007. There is 2005 and there is 2008, but no 2007. So, is it 2005 or 2008?

Second, you are using a connection string to an Access database not a SQL Server Database, so you are going to have to change that.

Go to http://www.connectionstrings.com to get the correct one for your version of SQL Server Express.

Ah sorry it's 2008.

I've got recordsets working with the tables now using these connection strings, although I'm a bit confused what to do about queries? These are not server side so it is not finding the object if I use the SQL server connection string, and if I use the access database connection string I get the same 'ODBC error'. I could migrate my queries to the server side, but if I do that I cant seem to link up forms to the query. Do I need two queries, one for form linking and one for code recordsets? Or is there a way round this?

 

[boblarson]

SQL Server 2008 may require you to use the SQL Native Client (10.0) driver instead of the SQL Server ODBC driver. You might try using that one instead.

Try using

Provider=SQLNCLI10;Server=myServerAddress;Database=myDataBase; Trusted_Connection=yes;

Be sure to change the parts in red to your own info.

 

[303factory]

SQL Server 2008 may require you to use the SQL Native Client (10.0) driver instead of the SQL Server ODBC driver. You might try using that one instead.

Try using

Provider=SQLNCLI10;Server=myServerAddress;Database=myDataBase; Trusted_Connection=yes;

Be sure to change the parts in red to your own info.

Hmm I see, thanks for this info, using this driver is working fine for me from code but I'm a little confused about linking tables. When linking tables with the 'get external data' I have to choose a data source, but the SQL Native Client driver does not appear on the list of drivers when I try create a new DSN.. should it be or am I doing something wrong here? (I have had problem making the MySQL ODBC driver appear in this list so want to make sure I dont have a registry problem)

 

[303factory]

ps i'm a general newbie with databases and have been thrown in at the deep end with a large front end client so please excuse my general ignorance on the subject, if you can recommend a general tutorial I would appreciate it.
I've got my eye on 'Microsoft Access Developer's Guide to SQL Server' by Andy Baron.. although I'm not sure how up to date it is.

 

[303factory]

Once again I kinda fixed this myself.

SQL Server Native Client was not appearing in my drivers list due to a registry error. Had to delete a '@=""' line from my ODBC registry and re-import it and it worked.

Still interested in recommendatations for Acces-SQL server books/online tutorials.

 

[Banana]

I'd go for Andy Baron & Mary Chapman's book on Developing Access with SQL Server. Fairly dated but very well written by someone who know both programs in and out.

 

[303factory]

Ok thanks