Access connects fine, but I got problems with SQL

[neroe]

Any help with this is greatly appreciated, thankyou.

I currently use the below VBScript to connect to a Access database and it all works perfectly.

<%
'Request the name of cars and the primary key from the Cars table.
SQLCAR=&quot;SELECT carName, carID FROM Cars&quot;
set conncar = server.createobject(&quot;ADODB.Connection&quot
conncar.open &quot;parking&quot;
set cars=conncar.execute(SQLCAR)
%>

What I now want to do is exactly the same to a SQL 2000 database.

I have imported the database and set the ODBC (and tested it successfully)

Now I beleive that if the above code is altered then I can make the connection?!?!?

 

[SonOfEmidec1100]

If you are making an ADODB connection you dont need a DSN. There is more than one way to do this heres a basic example, you will need to adjust the connection string. also Ive added an order by to the sql.

Dim sqlcar As String
Dim conncar As ADODB.Connection
Dim cars As ADODB.Recordset
Set cars = New ADODB.Recordset
Set conncar = New ADODB.Connection

sqlcar = &quot;SELECT carName, carID FROM Cars ORDER BY carName&quot;
conncar.ConnectionString = &quot;Provider=sqloledb;Data Source = <ServerName>;Initial Catalog=<databasename>;Trusted_Connection=Yes;&quot;
conncar.Open
cars.Open sqlcar, conncar, adOpenDynamic, adLockOptimistic

 

[deeshubby]

I've imported a few web applications into SQL Server that were originally based on Access, before.

One thing I ran into was that any MEMO field types in Access that might be converted to a &quot;blob&quot; field like 'ntext' in SQL Server.. those fields should appear LAST in your SELECT statements. Don't ask me why.