Need Access connection string converted to SQL

[meldrape]

Greetings, I feel foolish asking for this. I have this script and SQL database but the script is designed for an access database. I need the connection information converted to SQL but try as I might, I keep getting various errors. There is something I'm not doing right. Here's the original code:

Sub subConnectionOpen
Set objCon = CreateObject("ADODB.Connection")
Set objRS = CreateObject("ADODB.Recordset")
sProviderName = "Microsoft.Jet.OLEDB.4.0"
iCursorType = 1
iLockType = 3
sDataSource = "\\MAINSERVER\C$\pcinfo.mdb"
objCon.Provider = sProviderName
objCon.Properties("Data Source") = sDataSource
objCon.Open
objRS.CursorType = iCursorType
objRS.LockType = iLockType
objRS.Source = "Computerinfo"
objRS.ActiveConnection = objCon
objRS.Open
End Sub

Any thoughts would be helpful. Thanks in advance!

 

[Dashley]

Meldrape,

Sql is Sql and connections are connections. What are you trying to connect to? MYSQL, SQLServer ORacle, MSSQL SERVER?

Here's a sample connection string below to MYSQL followed by one for oracle

Yours would probably be.

objcon.open ="Driver ="Microsoft.Jet.OLEDB.4.0;Datasource=\\MAINSERVER\C$\pcinfo.mdb;UID=MyUiD; PWD=MyPWD. "


Hope this helps

Dan

conn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};"_
& "SERVER=localhost;"_
& " DATABASE=test;"_
& "UID=MyUid;PWD=MyPwd"

CONNSTR = "Provider=MSDAORA.1;User ID='" & USERID & "';PASSWORD='" & Password & "';Data Source='" & SERVERNAME & "';Persist Security Info=true"

conn.open CONNSTR

 

[meldrape]

Thanks, Dan. It's a SQL2000 database I'm trying to connect to. I'll keep playing around with it. Thanks again.

 

[EBGreen]

I recommend checking here:

http://www.connectionstrings.com/

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[meldrape]

Thanks for that link, Tom. I just figured out how to do the standard SQL connection and it's working fine now. Thanks again!