ACCESS DSNless connection to SQL

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi!

I have an Access front end with linked SQL tables through a DSN. I need
to distribute the application, but, I can't be creating DSN's on user
workstations. Nor can I create DSN's on the fly. And, no, I can't use an
ACCESS project for this application either.

I thought there was a way I could change the connection attributes of
linked tables so that they were linked using an ado connection rather than an
odbc connection, but I can't quite figure that out.

When you right-mouse click on one of the linked tables and select "Design
View", you're taken to the screen that lets you see all the tables fields and
all their properties. From there, if you select the "Properties" button from
the toobar at the top of the screen, that opens up the "Table Properties"
information. Under "Description" I see:

ODBC;DSN=Budget;Description=Budget;UID=Cosmos_Sql;APP=Microsoft Office
2003;WSID=COSMS;DATABASE=Budget;Trusted_Connection=Yes;TABLE=dbo.BudgetInfo

In this connection information, Budget is the name of the DSN, Cosmos_Sql is
the name of the instance of SQL Server that's running on a server on our
network called "COSMOS" The database is Budget and the table name is
"BudgetInfo".

Can I just rewrite this line of information in "Description" to be an ADO
connection instead? What would the syntax for that be? That's partly where
I'm confused. Not only do I NOT know if this will work at all; I don't even
know the correct syntax to make it work if it's supposed to.

Is there another or better way to change the table connections to be DSNless
so my users won't need DSN's and I don't have to work through ODBC?

I know this sounds bad, but we all know that an application is rarely
finished, so I'll probably wind up changing the tables a lot, adding tables,
modifying field attributes, etc. Right now, all I have to do is go into the
Linked Table Manager and relink all the tables to straighten things out, or
link a new table if necessary. I often create views of data to use for
reports, and I'm always adding fields as I need them for a new report or
query, so I need to make sure I can link the views the same way I do now, as
though they were tables.

If I change the table link information in the description to DSNless (ADO)
connections, will the changes I make within SQL Server just automatically be
there or will I continue to need to 'relink' them?

Any advice or guidance would be greatly appreciated.

Thanks!
Karen Grube
 
Hi Amy!

Thanks for answering! I've seen this article. It looks as though it
still uses an ODBC connection rather than ADO, and I can't figure out how to
adapt Doug's code so that it uses ADO. I think all I have to do is modify
the following lines, but I'm not sure.

Set tdfCurrent = dbCurrent.CreateTableDef(typNewTables(intLoop).TableName)
tdfCurrent.Connect = "ODBC;DRIVER={sql server};DATABASE=" & _
DatabaseName & ";SERVER=" & ServerName & _
";Trusted_Connection=Yes;"
tdfCurrent.SourceTableName = typNewTables(intLoop).SourceTableName
dbCurrent.TableDefs.Append tdfCurrent

Perhaps you could show me what this code should look like creating an ADO
connection using Doug's technique. Here's the original information:

ODBC;DSN=Budget;Description=Budget;UID=Cosmos_Sql;APP=Microsoft Office
2003;WSID=COSMS;DATABASE=Budget;Trusted_Connection=Yes;TABLE=dbo.BudgetInfo
I'm having trouble figuring out where quotation marks should go, where to
substitute in my information, and how to rewrite this for ADO. We can easily
hard-code in the server name and the database name, if that helps. The
connection will still be a "trusted" connection. The only variable would be
the table name. And yes, I do understand about the unique identifier. Every
table has one, so that's not a problem.

All I think I need at this point is to see an example of what this
particular section of code would look like as an ADO connection. If you
could please help with that, I'd appreciate it.

Thanks!
Karen
 
To the best of my knowledge, there isn't any way to do what you seem to be
asking. You can certainly access (no pun intended) data using ADO, by
programmatically opening ADO Connection and Recordset objects. However, to
the best of my knowledge, the linked table feature of Microsoft Access can
not use ADO. As far as I am aware, to use linked SQL Server tables in an
Access MDB, you must use ODBC.
 
Amy and Brendan,

Oh, I get it now! I'm sorry. For some reason, I was thinking there should
be a way to link tables within ACCESS through ADO.

Amy, though I had more specific syntax and coding questions, your article on
databases was terrific for beginners!

I'll try using Doug's technique and see how that works.

Thank you both so much! I truly appreciate the help. Hey, Doug's article
should be in Microsoft's KB, or perhaps on MSDN! As far as I can tell,
there's nothing like that there.

Thanks again!
Karen
 
AFAIK, Access forms use DAO to connect to tables, whether internal or
linked, since that is what Jet uses natively. One thing that IS in the
presentation that I did not mention is that ODBC connections are the only
ones that actually allow DSN'less connections. Or I think it's in that
version...I've made some revisions since.

Even if you have syntactical questions, a solid understanding of what's
going on "under the hood" helps understand what alterations need to be made
in syntax to conform to your specific situation.

Have a great day;

Amy
 
Back
Top