openrowset or linked server

[Saama5]

How can I use .udl file with openrowset or linked server command without pasting.

 

[jhall156]

I can't see a way to do it. Why do you need to? JHall

 

[Saama5]

I am using direct command
Insert into xx
select * from openrowset('MSDASQL.1'....)
I don't want to use ODBC and want to use Microsoft.Jet... and flexible databasename which could be changed from udl file.

 

[jhall156]

Yeah mutable database/table names would make the udl most useful. How about using COM to control DTS Package objects or perhaps programmatically changing your linked server properties? JHall

 

[Saama5]

Any help to change linked server properties. That would be helpful at this time.

 

[cmmrfrds]

If you are in an Access Application, for example, you can dynamically control the content of the SQL string including the connection string. Is this acceptable? Also, if you use the OLE provider for SQL Server for your connection it will not use ODBC, as it does in the sample code you are showing.

 

[Saama5]

I am trying to Access file where I want to change absolute path many a time or filename.
I am Inserting into SQL
pConn->Execute(strSql....)
where strSql is
Insert into abc
select * from openrowset('MSDASQL.1',...)
now I want to remove this odbc with oledb using udl file. So I will ask user to change .udl file if they want.

 

[cmmrfrds]

What would be the advantage of putting the database name and location in the udl as opposed to the user entering the information in a Form and you programmatically build the correct strings with the location and database name? Even if there was a udl file, it seems to me that you would need to build in the openrowset connection string the path name to the udl file at least.

 

[Saama5]

Thanks! I will try to do programmatically.

 

[cmmrfrds]

If you are interested else ignore. Here is some sample code from Access that uses Northwind from SQL Server and the Northwind mdb installed with Access.

Public Function rowset()
Dim cn As New ADODB.Connection, sqlString As String
Dim rs As New ADODB.Recordset, connString As String
connString = "provider=SQLOLEDB.1;" & _
"User ID=sa;Initial Catalog=northwind;" & _
"Data Source=bigtuna;" & _
"Persist Security Info=False"
cn.ConnectionString = connString
cn.Open connString

sqlString = "SELECT c.*, o.* "
sqlString = sqlString & "FROM Northwind.dbo.Customers AS c INNER JOIN "
sqlString = sqlString & "OPENROWSET('Microsoft.Jet.OLEDB.4.0', "
'--- could put variables in at this point for location and database name
sqlString = sqlString & "'c:\program files\microsoft office\office\Samples\northwind.mdb';'admin';, Orders) "
sqlString = sqlString & "AS o ON c.CustomerID = o.CustomerID "

rs.Open sqlString, cn, adOpenForwardOnly, adLockReadOnly

If Not (rs.EOF = True) Then
Debug.Print "field name = "; rs.Fields(0).Name
Debug.Print "field value = "; rs.Fields(0).Value
End If

End Function

 

[Saama5]

Thanks! Yes you are right, I should use it programmatically instead of just getting mad for .udl file.