mde access database front end to to SQL

[Pietro]

Hi all,

Can we link an mde access database front end to to SQL ?

 

[Stefan Hoffmann]

hi Pietro,

Can we link an mde access database front end to to SQL ?

To cite someone: Yes, we can!

A .mde is basically only a source code stripped .mdb. You can still
create, modify or delete tables and queries.

mfG
--> stefan <--

 

[Pietro]

Ok,

If i have an mde databse can i connect it to SQL be or not ?

 

[John Spencer]

Yes, you can. It is easiest to set up the linking BEFORE you convert to an
mde, but it can be done with an mde and vba code that you call.

If you mean you want to relink to a different SQL backend, that is possible
but you will need some VBA code to do the relinking.

One method that works well and does not require setting up a DSN is described
by Doug Steele

Take a look at
http://www.accessmvp.com/djsteele/DSNLessLinks.html

Also Check Carl Prothman's site
http://www.carlprothman.net/Default.aspx?tabid=90#ODBCDriverForSQLServer
for the appropriate connection string to use.

Also check
http://www.connectionstrings.com/



John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[JimS]

Pietro,
What Stefan was saying is that if the .mdb file from which you make a .mde
file references SQL Server data, then, the .mde will reference that same
database. You cannot change the references (table links) that a .mde file has
without changing them in the underlying .mdf front end. If an existing .mde
file points to an Access back end, it cannot be changed. You must create a
new .mde file from the underlying .mdf front end. If you don't have the
underlying .mdf front end, you can't change it.

 

[Tom van Stiphout]

On Wed, 3 Dec 2008 05:26:02 -0800, Pietro

Yes, we can!

-Tom.
Microsoft Access MVP

 

[JimS]

John, thank you for enlightening me!
--
Jim

Yes, you can. It is easiest to set up the linking BEFORE you convert to an
mde, but it can be done with an mde and vba code that you call.

If you mean you want to relink to a different SQL backend, that is possible
but you will need some VBA code to do the relinking.

One method that works well and does not require setting up a DSN is described
by Doug Steele

Take a look at
http://www.accessmvp.com/djsteele/DSNLessLinks.html

Also Check Carl Prothman's site
http://www.carlprothman.net/Default.aspx?tabid=90#ODBCDriverForSQLServer
for the appropriate connection string to use.

Also check
http://www.connectionstrings.com/



John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Golfinray]

I use an ODBC connection. Google ODBC.

 

[Pietro]

Thank you Tom for your reply.
My case exactly is : I'm linking an MDE frontend to an MDE backend.I've
already lost the MDB file, can i upsize the backend and make it SQL database
and link the MDE frontend to it ?
That's all.

 

[Brendan Reynolds]

Thank you Tom for your reply.
My case exactly is : I'm linking an MDE frontend to an MDE backend.I've
already lost the MDB file, can i upsize the backend and make it SQL
database
and link the MDE frontend to it ?
That's all.

Probably not, no. Your VBA code is likely to need some modification to work
with SQL Server - for example, you'll have to add the dbSeeChanges option to
any OpenRecordset statements where the underlying table has an identity
field. And you can't make those modifications to an MDE.

 

[Pietro]

Ok, if could get an mdb backend, can i upsize it with SQL and link it to my
mde frontend?

 

[Rick Brandt]

Ok, if could get an mdb backend, can i upsize it with SQL and link it to
my mde frontend?

Make a copy and test it. As Brendon points out some things don't "just
work" when you move to a SQL back end, but most things do. Your
particular app might work fine with a SQL back end or it might turn into
a steaming pile of crap. There is inly one way to find out.

 

[Brendan Reynolds]

Ok, if could get an mdb backend, can i upsize it with SQL and link it to
my
mde frontend?

Creating an MDE from an MDB makes no changes to any tables or queries that
the MDB may contain, so it makes no difference whether your 'back-end' data
file is an MDB or MDE, it's the 'front-end' application file that matters.
As others have said, you can try it and see, but in my opinion it is
unlikely that any non-trivial app will function in an acceptable manner, if
at all, without some modification. Some apps may need a lot of modification,
some may need only a little, but very few will need none at all. And in
order to make any modifications you need to recover or recreate the MDB.