Access and SQL server

[lingyi]

Hi there,
Anyone can explain to me after I upsizing my access database to SQL server yet my access still as front-end application, How did these two database communicate with each other, after upsizing, will my access database still be used? I planning to upsizing my access database yet access still as front-end application, yet i dont know what is the consequences after I do so, so i want to make clear before I really did it

 

[Kindi]

To use the access tables/forms as the frontend you need to link tables, between the SQL Server and Access. You need to create a ODBC connection to the SQL Server database via the control panel, then in access choose file, import data, link tables, select the ODBC source name, created in control panel.

 

[Guest_imported]

hi there,
If I want to edit my access application so that it will directly connect to SQL server. What command I should code,please give examples and explanations. Does a link table in access to SQL server will slow down the process between local network and wireless network

 

[lingyi]

hi there,
If I want to edit my access application so that it will directly connect to SQL server. What command I should code,please give examples and explanations. Does a link table in access to SQL server will slow down the process between local network and wireless network

 

[lingyi]

hi Kindi,
But i really dont understand ur explanation on the linked table. So I still cant connect my application in access to sql server although I already upsizing it

 

[vr]

I use cndb.connection. The Microsoft Knowledge Base has several examples.

 

[lingyi]

Hi vr,
What you means by cndb.connection? Dont I hv to declare something in term to use this command? Pls explain to me..thanks

 

[petermeachem]

First create a dsn. Select ODBC in the contol panel and add a new dsn that points to your data.

It then depends on your version of Access. With 97 go to File/Get External Data/Link Tables. Under Files of Type select ODBC databases. You will then see a list of dsn. Select the one that you have set up for your database, after a few more clicks, you will see a list of tables. Select them. Click a bit more and you will have a list of attached tables. You will notice that if you had a table called Things, you will now have a linked table called dbo_Things. On the tables tabs in your front end Access programme, rename the linked tables so there are the same as you originally had.
You will find a few issues when running the programme, but Access explains what you need to do. Peter Meachem
peter@accuflight.com

 

[mattpearcey]

Slightly off this subject. I am considering upgrading my access 97 database into a front end system from SQL, but have no idea about how to go about it. But i will probably use other programmers to assist me in this process. However, when woudl you consider a database in 97 access to be too big. I currently have 1 main form coming out of a couple of large table. This main form has 9 tabs, all containg a great deal of data. Connected to this, ihave a further two forms, both coming from their respective tables, and both containg a lot of data. I have about 400 records, which are details of banks. So, quite indepth info.

I am pretty new to this, but would just like to know how far i can go before i will need to upgrade?

any advice? Thank you for your help already.

Matt Pearcey

 

[petermeachem]

I'm not too sure that an sql back end would be that much better. How many users are there? A whole lot of subforms generally means a whole lot of network traffic, although you have so few records that may not amount to much.

For comparison, I have a customer still using a vb3 + access 2 programme. They have 10 users all reading and writing records as fast as they can. The database is round about 200Mb and has 1/2 million records. It works ok, reading and writing single records. I'm supposed to be making it a bit more up to date when I have time, i.e Access 97 and VB6. Peter Meachem
peter@accuflight.com

 

[mattpearcey]

Peter

Im a long way of getting to yor stage. Although i take your point about traffic and users, so i will bear that in mind. But other than that, you have answered my question. And what i have if fine, and will be for a while i can imagine.

Thanks for you advice. Very much appreciated. Thank you for your help already.

Matt Pearcey