We are moving our backend to SQL Server from the existing Access
database.The front end is still Access.I have set up the ODBC
Connection to the new SQL Server.How to access the table information in
SQL from the Access database.I know this is simple and bear with me for
this silly question.

Also I loaded up SQL Server standard edition on my workstation.How do i
use the enterprise manager to access the tables on the remote SQL
server.

Thanks,

Roy

If you've created an ADP in access, and it is currently connected to
SQL Server, the tables show up in access as if they were in an access
database.. for the most part.

I want to do the same thing. But I only have a mdb file. Is it easy to
convert MDB to ADP?

Hi,

i run a multiuser .mdb (mde) using a SQL server backend.

set up your data source using the system DSN tab on the Data Sources(ODBC)
located under administrive tools.

you can then link your tables in access front end as normal. only you select
Databases (ODBC) as your file type to link

for a mulituser system also check out DSN Stripper. that way you wont have to
go around and set up the data source on each PC.

Lee-Anne

swingingming wrote:

--
Message posted via http://www.accessmonster.com

LeAnne,
Thanks much.It worked!

Hi Lee-Anne, I never used SQL server backend before, and I want to use
one. The database will be used by 7-10 people, from different locations
(not in a LAN). From what I know, I should put the frontend on the
client machines, and let them all connect to the backend server. The
server will be on a LAN behind a router. Is this possible?
Thanks.

Jim Devenish wrote:
"Trusted_Connection=Yes" means that the current network credentials are
used to establish the connection. Since the database requires a user
name and password, you should remove the Trusted_connection attribute or
set it to No. You should use the ODBC names for the user and password
attributes: UID and PWD instead of "User ID" and "Password". Also,
shouldn't that connection string be quoted? Like this:

aTable.Connect = "ODBC;APP=Microsoft Data Access" & _
"Components;Driver=SQL Server; Server=Server" & _
"\TRUTIMEV3;Database=Intelligent;UID=xxx;PWD=xxx;"

PS. I know you say it's been "working for years", but it's really not a
good idea to use implicit ADO connections. The line:
rec.Open theSQLString, theConnectionString ...
causes ADO to open a connection object behind the scenes (implicitly),
an object over which you have no control. If you are opening multiple
recordsets in your application, the result could well be that a separate
open connection to the database will exist for each recordset you have
opened, each of which consumes resources both on the client machine as
well as on the database server. Eventually, the point could be reached
where your database server refuses new connections. Best practice is to
explicitly create a connection object and use that to open your
recordsets:

dim cn as adodb.connection
set cn=new adodb.connection
cn.open "your connection string"
....
rec.Open theSQLString, cn, ...

Close the connection object when finished using it so that it goes back
into the pool of connections that can be re-used.

Also, you should get away from using ODBC with ADO as it creates an
extra software layer. Use the native OLE DB provider for SQL that is
relevant for the version of sql server you are connecting to. You can
find the relevant OLE DB connection strings here:
http://www.connectionstrings.com/




--
HTH,
Bob Barrows

On Nov 22, 7:02*pm, "Bob Barrows" <reb01... (AT) NOyahoo (DOT) SPAMcom> wrote:
Many thanks Bob. It is now working. I do not use/create connection
strings very often and so am not sufficiently familiar with all the
syntax. I had not appreciated that I needed UID and PWD instead of
User ID and Password for an ODBC connection. That did the trick.

Prior to last week there was no password protection on the SQL
database and so I had not needed these parameters before.

Thanks for the advice on the connection object.

The missing quotes were caused by a bit of copy and paste. They were
in my code!