SQL as back-end 3

[fule12]

Hi all,
I just have one small question. I just transfer my MSAccess(2000)back-end to sql server. But now i have question for access front-end. Which one to use access.mde or access.adp application to develop front-end application.

Thanks

Fule

 

[HFA]

access.mdb i suppose,
cuz many access application saves the data in mdb file
(forms,reports,queries,tables all saves in one mdb file)
But This is not the good way to miqrate sql database to access.
Cuz access database is poor database. espesially if there has much amount of data. It'work slowly and grows rapidly.
i prefer that you remain your database in sql server and
write me why do you want to transfer data to access.
thanx

 

[SQLBill]

HFA,

You missed the posters point. Fule DID transfer all the data to SQL Server. Fule wants to know what to use as a FRONT-END application (in other words, which of those options should be used to QUERY SQL Server).

Fule,

I don't use either one and while someone might be able to answer your question here, you might also want to post it on the Access forum.

-SQLBill

 

[philhege]

HFA,

You probably have a significant amount of objects (forms, reports, queries, modules, etc.) developed in the Access mdb. An ADP isn't really necessary, and I'm sure you don't want to recreate those objects in another front-end language. Upsizing the data to SQL is sufficient,unless you have other unstated issues with Access.

Good luck

Phil Hegedusich
Senior Web Developer
IIMAK

http://www.iimak.com

-----------
Boy howdy, my Liberal Studies degree really prepared me for this....

 

[fule12]

Thanks for your reply, i will post my question in Access forum.

Fule

 

[edwinjs]

I have little access knowledge. But I think you link SQL Server Tables to your Access Database and the data will still remain in SQL Server and all your forms and view can use the linked tables and views.

 

[BSman]

I've done this with a few applications and here are my observations/suggestions:

SQL back end works well with Access 97/2000 as front end. I have one app with the SQL DB with over 4 gig of data, some tables with over 2 millions rows of data (in each table).

I've now discovered that there is a limitation with fields that were text/memo fields in Access. The max amount of data must be limited to 254 characters (not 255...Access will choke when trying to "translate" 255 characters into a 255 character text field definition. However, the field should be nvarchar(255) in SQL, but in the Access application you need to include code to limit the actual data to 254 characters (don't ask why!). Memo fields just plain don't work. The data will be imported into SQL fine, but there is not field type of "memo" in SQL and the field type used by SQL is not recognized or handled by Access.

I looked into using Access Project (2000 and up), but there are some serious problems. Although you can define/create views and stored procedures directly with Access 2000 project, when you go to Access 2002 (and above) project you will NOT be able to create them. So if you have any part of your application that creates queries, etc., they will no longer be usable in Access Project. This eliminates a query builder I designed in Access that has proven to be extremely useful and user-friendly. (It creates pass through queries that end up being executed by SQL server, so there is efficiency because they are not executed by the Access jet engine.)

One thing that is very handy with the Access 97/2000 front end is that you can include tables that are Access tables within the front end application. The only tables to include should be ones that are used for control of the application (and data is not entered or edited by users). Since Access project does not have any "local" (i.e., stored with the application front end) tables, you lose this capability unless you put the tables in SQL server.

Except for the limitation in the size of the text field and the inability to use memo fields, it works extremely well. And no more shutting down your application in order to compact the back end (data) database! But don't forget to occasionally copy the "original" front end over the one on your server, since Access does have the annoying habit of getting slightly larger every time someone opens it.

 

[Coxylaad]

Hi,
Just to add another question. How do you link the tables in from the SQL server into Access?
I have tried using an ODBC link but it seems to offer very poor performance - are there any other options?

Thanks

Ian

 

[BSman]

Something to add to what I wrote about using a SQL back end with an Access front end. You can handle memo fields with the SQL back end. When they are converted into SQL using DTS the memo (and larger text) fields are converted to "ntext" type fields that default at a size of 16. While Access will choke on these fields, since it doesn't recognize them, if you change them to "text" fields (that will still default to a size of 16), they are handled like memo fields by Access.

All my apps are linked through ODBC and the performance has been stellar. Ian, you might try re-defining the system definition of your database under ODBC, and then re-link the tables from Access to SQL Server.

By the way, I've noticed that if you use the link manager in Access to change the links Access will no longer remember the userid and password (if you had originally specified that they should be saved).

 

[christywarner]

I also want to migrate my back-end to SQL Server,
with an Access 2000 front-end. Do any of you have
some Access code to link to SQL Server? I have
developed some w/ the ADODB.connection object,
and have successfully created & deleted tables
on my SQL Server Developer version.

Just looking for some good sample code -

and - doesn't SQL server handle terabytes of data?
What is it's limit?

Will the SQL Server's power be limited by Access 2000
front-end in any way? Performance?

Thanks so much,
Christy.

 

[BSman]

You don't need code to link to SQL Server (7 or 2000) from Access 2000 (or 97). First set up an ODBC connection to your SQL database. (In XP go to Start/Settings/Control Panel/Administrative Tools/Data Sources, then System tab and add a SQL connection to your SQL database.)

In Access you then select File/Get External Data/Link Tables. Files of type ODBC Databases and select the SQL database you just defined.

I usually set up a password in SQL for internal connection use and check off the "Save Password" box in Access when defining the links.

You now use the tables as if they were in Access (see my earlier notes).

As far as performance, it is very good. The biggest SQL database I'm using has about 4 gig of data, with a few tables with 1.5-2.5 million rows of data, and the response is generally quite fast. One trick is to define your queries (as much as possible) as pass through queries, which really means that you are passing SQL Server SQL code directly to the server, where it is executed and only the results returned back to Access. So you need to create the pass through query in your code. I use the same query name all of the time and just make sure I delete it before redefining a new version of the query.

Here's a sample of code to create a pass through query. Don't forget to delete the query first if it already exists:

Dim strSQL As String
Dim dbs As DATABASE
Set dbs = CurrentDb
Dim qdf As QueryDef, rst As Recordset
dbs.QueryDefs.Delete ("Matching records")
Set qdf = dbs.CreateQueryDef("Matching records")

' set connection string for standard userID
'and password. Reset ODBC timeout to 300 seconds to
' reduce change of ODBC failure for large queries.

qdf.Connect = "ODBC;" & "DSN=TheSQLDbName;" & "UID=UserID;" & "PWD=password;" & "DATABASE=TheSQLDbName;" & "Address=servername,port"

qdf.ODBCTimeout = 300
strSQL = "SELECT * FROM MyTable WHERE condition " &_
"ORDER BY columns;"
qdf.SQL = strSQL
DoCmd.OpenQuery "Matching records"
Set dbs = Nothing