Using Access 2003 with SQL Server...?

**MGFoster** · Nov 13 '05, 09:08 AM

Re: Using Access 2003 with SQL Server...?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You can create the tables on Access & then Export the table to SQL
server. How:

1) Create the table in Access. Fill it w/ data if desired.
2) In the database window, highlight the table.
3) On the menu bar select File > Export.
4) In the "Export Table 'table name' To..." dialog box, in the "Save as
type" ComboBox select ODBC Databases.
5) In the next dialog box enter the name of the table you want on the
SQL Server. Hit enter key.
6) Select the DSN (or create it) to the SQL Server db where you want to
put this table. Click the OK button.

The table & its contents will be copied to the SQL Server.

If the table's you copy have AutoNumber columns in Access they will be
saved as Integer columns in SQL'r. You'll have to change them in SQL'r
to Identity columns. The values will not change when the Identity type
is applied to the column.

When you've completed that for all the tables you want on SQL'r, delete
the Access versions & link to the SQL'r versions. Treat the linked
tables just as you would an Access table.

--
MGFoster:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQjOLLIechKq OuFEgEQJ8rQCgn2 m71UkYiAnmGUU6R To3jGmh8EcAn2Jh
Set09t/HPbJBRnwz03FWhq Bc
=xBsd
-----END PGP SIGNATURE-----

meyvn77@yahoo.c om wrote:[color=blue]
> Background: I'm new at SQL Server (no experience, zip, nada) but
> getting pretty good with developing applications in Access(VBA) and in
> VB using ADO....
>
> Currently I use access to and GIS software to build data management
> systems. I have become pretty proficient in programming these systems
> in Access but a client would like their system to be on an existing SQL
> server.
>
> I understand one way to go would be to develop the GUI in VB and hit
> the SQL server using ADO which I have worked with in the past but
> really only 3 or 4 tables out of the 35 or so really need to be used by
> the GIS thus only 3 or 4 tables really need to be on the SQL server.
>
> My question is: can I use Access as the front end for all my imports,
> queries, and other system code and then when it comes time to write the
> final tables that the GIS hits write those tables to the SQL server.
> Also I would like to access the SQL Server tables though Access. I
> would like ant help on the subject or any links to recourses that could
> help me in deciding which direction to go...[/color]

**rsinclair@metacrawler.com** · Nov 13 '05, 09:08 AM

Re: Using Access 2003 with SQL Server...?

Hey Chuck,
You can directly link SQL Server tables to Access using ODBC if you
want to keep your other tables in Access. Choose the Link Tables option
from right-click menu in Tables list view and when it asks for a file,
open the "Files of Type" drop-down in the dialog and choose "ODBC
Databases()". This will take you into a dialog where you can
create/select an ODBC datasource and link the tables. The Access help
should have more information on this or check the MSKB.

Cheers,
Russell Sinclair

Using Access 2003 with SQL Server...?

Using Access 2003 with SQL Server...?

Comment

Comment