DSN connection - not wanting to use

[lbunch]

If I do not want to use a DSN to connect to a database - what do I use. I am using the below for a connection and getting prompted ODBC error.

Will this work:
Dim oRS As ADODB.Recordset
Dim oConn As ADODB.Connection
Dim oCmd As ADODB.Command
Dim strSQL As String

Set oConn = New ADODB.Connection
oConn.Open "Provider=sqloledb;" & _
"Server=sql901;" & _
"Database=EmpLibrary;" & _
"User Id=SQLViewUser;" & _
"Password=SQLViewUser"
'On Error GoTo 0
If oConn.State <> adStateOpen Then Exit Sub
Set oRS = New ADODB.Recordset

 

[PHV]

Have a look here:

http://www.connectionstrings.com/

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lbunch]

I looked there but not sure which I use for access database to sql server. OLEDB??? I thought I would use ADO but not seeing a connection string there for ADO.

 

[lbunch]

I am trying this - I am getting an "expected line number error on the first line.


"Provider=sqloledb;Data Source=sql901;" & _
" Initial Catalog=EmpLibrary;" & _
"User Id=SQLUser; & _
" Password=SQLUser"

 

[PHV]

Replace this:
"User Id=SQLUser; & _
with this:
"User Id=SQLUser;" & _

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lbunch]

thanks, will this be all I need or will need more code like below I got from another post below - which is better??? do you think???

Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table

Set cat = New ADOX.Catalog
Set tbl = New ADOX.Table

Dim sConnString As String

' Set SQL Server connection string used in linked table.
sConnString = "ODBC;" & _
"Driver={SQL Server};" & _
"Server={Boxer};" & _
"Database=ReportInventory;" & _
"Trusted_Connection=No;" & _
"Uid=rptinvapps;" & _
"Pwd=yourpassword;"

' Open the catalog.
cat.ActiveConnection = CurrentProject.Connection
For Each tbl In cat.Tables
If tbl.Type = "LINK" And tbl.Name = "tblReportRequestPrelim" Then
tbl.Properties("Jet OLEDB:Link Datasource") = sConnString
End If
Next
End Function

 

[lbunch]

I am still getting an error for line number - this is what I have changed to now. Is this all I need?????

Private Sub Option164_GotFocus()
Dim oRS As ADODB.Recordset
Dim oConn As ADODB.Connection
Dim oCmd As ADODB.Command
Dim strSQL As String


"Provider=sqloledb;" & _ < still getting error
"Data Source=sql901;" & _
"Initial Catalog=empLibrary;" & _
"User Id=SQLUser;" & _
"Password=SQLUser"

If oConn.State <> adStateOpen Then Exit Sub
Set oRS = New ADODB.Recordset
Me.cmbempID.RowSource = "SELECT emp_id FROM dbo_resplan_view ORDER BY emp_id"
End Sub

 

[PHV]

Are you some "cargo cult programming" adept ?

 

[lbunch]

Well - duh - I changed my code and thought using oledb would be the way to go but that was the way that was causing an ODBC error - so I am really just totally lost.

I don't know what you mean - I am just looking for the best way to connect to a sql server database dsnless. Obviously the oledb is looking for a dsn/

 

[lespaul]

DSN
Short for Data Source Name. Data Source Name provides connectivity to a database through an ODBC driver. The DSN contains database name, directory, database driver, UserID, password, and other information. Once you create a DSN for a particular database, you can use the DSN in an application to call information from the database.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[lbunch]

I can't use a DSN connection. Thanks _ I need to connect dsnless and like to a table

 

[lespaul]

explains why you were getting an ODBC connection error though doesn't it?

 

[lbunch]

Yes and that is why I am asking you guys how to get around using a DSN - thank you.

 

[lespaul]

googled: connect database without DSN ODBC

and found this at:

http://www.stardeveloper.com/articles/display.html?article=2000120101&page=1

[tt]DSN less Connection
DSN less connections don't require creation of system level DSNs for connecting to databases and provide an alternative to DSNs. We will now see how to connect to a database via ASP using Connection String in place of DSN name.

<%
Dim con
Set con = Server.CreateObject("ADODB.Connection")

con.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data" & _
"Source=c:\path\to\database.mdb"

' Now database is open and we are connected
' Do some thing here
'We are done so lets close the connection

con.Close
Set con = Nothing
%>

Explanation
The only change is use of a Connection String in place of a rather easy to remember DSN. Above code connects to an imaginary Access database. Connection Strings for other databases are different.

How to construct a Connection String for Access and SQL Server Databases ?

* For Access database :-
With native OLE DB Provider ( preferred ):
Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\path\to\database.mdb

Using ODBC connection without specifying a DSN :
Driver={Microsoft Access Driver (*.mdb)}; DBQ=c:\path\to\database.mdb

Note, always use the first Connection String that uses native OLE DB provider because it is faster than the second one. 'Data Source' or 'DBQ' are absolute path to the database. If you have relative path then you can obtain absolute path by using Server.MapPath("/relative/path/to/database.mdb") e.g.

Dim conStr
Set conStr = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
Server.MapPath("/dbo/database.mdb")

* For SQL Server :
With native OLE DB Provider ( preferred ):-

Provider=SQLOLEDB; Data Source=server_name; Initial Catalog=database_name; User Id=user_name; Password=user_password

Using ODBC Provider :
Driver={SQL Server}; Server=server_name; Database=database_name; UID=user_name; PWD=user_password

Why to use DSN Connections ?

* Provides easy to remember data source names.
* When there are lots of data sources to think of and you want a central repository to hold the collection of data sources without having to worry about the actual site and configuration of the data sources.

Why to use DSN less Connections ?

* When you can't register DSNs yourself e.g. when you are running a virtual hosting account on other's server. Stop emailing system administerator, connect to your databases directly.
* Provides faster database access because it uses native OLE DB providers, while DSN connections make use of ODBC drivers. [/tt]

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[lbunch]

Thanks - that helps