update a SQL 2005 Express table with an MS Access 2003 table

[dan2bhm via AccessMonster.com]

I've been using MS Access for the past 5 years, and I've never ventured any
further. I am now working on creating a more robust application using Visual
Web Developer 2005 and SQL Server 2005 Express.

Before I get too far into development, I want to ensure that I can upload my
Access table into my SQL database.

I started by creating a SQL database and a new table with all the columns
that I'd like to import.

I tried using SQL Server Management Sudio Express, but I don't know how to
bring in my Access database so that I can run an INSERT TO query.

Basically, I'm lost

From here, I'm COMPLETELY lost. I know Visual Web Developer and SQL 2005 is
new technology, but I'd really appreciate any assistance in getting this to
work.

Thanks

 

[PBsoft]

I've been using MS Access for the past 5 years, and I've never

ventured any further. I am now working on creating a more robust
application using Visual Web Developer 2005 and SQL Server 2005
Express.

It should not be the same: while with Access you create a Windows application,
with VWD you create a web application.
Anyway, I suppose you know

Before I get too far into development, I want to ensure that I can
upload my Access table into my SQL database.

Yes, you can.

I started by creating a SQL database and a new table with all the
columns that I'd like to import.

Here is the error. You don't have to use an import solution.
You have two choices.

1) You may use the upsize feature available in Access.
With the upsize feature you will be able to create the database and pass
all data. It's a wizard driven feature, so it's simpler to do than to explain.

I tried using SQL Server Management Sudio Express, but I don't know
how to bring in my Access database so that I can run an INSERT TO
query.

2) You may link a SQL table via ODBC.
File -> Load external data -> Import
Select "ODBC databases()" as filetype.
A dialog will appear, in which you have to define a custom DSN which point
to you SQL Server database: my suggest is to use "SQL Server" driver and
not "SQL Native Client", because the last is not well accepted by Access.
Another suggest is to use SQL Server authentication.
Once DSN is created, you will be led to a dialog where you will choose the
tables you want to import: select the table which you like to copy data into.
Warning: the table name will be similar to dbo.tablename!!!!
That's almost all.

Basically, I'm lost

Keep quiet

 

[dan2bhm via AccessMonster.com]

Thanks for the help. I got the Upsizing wizard to work, but I'm curious as
to how to use the DSN name.

Do I literally type "SQL Server" or is there a particular parameter that I
need to identify?

Also, once I exported this data to a SQL Server database, how do I link that
data back to MS Access? I'd like to use Access as a front end for some users
that may be more comfortable building queries in Access as opposed to
writeing SQL statements. I've tried the following:

click new table, select link table, then select ODBC Connections. It brings
up that window that you referred to with the File Data Source and Machine
Data Source tabs. I'm not able to locate the database, even though I
navigate to the appropriate folder where the SQL dB is located. Am I missing
some sort of setting in the SQL db??

Thanks again, for helping me understand this stuff.

 

[PBsoft]

Thanks for the help. I got the Upsizing wizard to work, but I'm

curious as to how to use the DSN name.

Ok. Let's investigate more.

Do I literally type "SQL Server" or is there a particular parameter
that I need to identify?

I don't understand your writing. The procedure to create a DSN connection
is completely automated. The only thing you have to type is the password
for the SQL login you would like to use to connect.

Also, once I exported this data to a SQL Server database, how do I
link that data back to MS Access?

It depends. Two possibilities: MDB+ODBC, ADP+OLE DB.
I suggest the second method, which was build by Microsoft specifically for
SQL Server.

I'd like to use Access as a front
end for some users that may be more comfortable building queries in
Access as opposed to writeing SQL statements.

Ooooppssss.....
I see a problem.

If you use MDB+ODBC you can continue to use Access as you did before, but
you will not be able to see relationships, and will have often to create
Pass-Through queries.
If you use ADP+OLE DB you will be able to use all SQL objects, as you should
have done with SQL 2000, but you MUST notice that all actual Access versions
CANNOT manipulate or create queries, stored procedures, functions and so
on in SQL 2005. You will have to create the separately, e.g. via SQL Server
Management Studio, if you are not so able with SQL statements.
Anyway, I continue to suggest to use the second method to connect to a SQL
database, no matter the version.

click new table, select link table, then select ODBC Connections.

Right. This is the first method, which uses ODBC.

It
brings up that window that you referred to with the File Data Source
and Machine Data Source tabs. I'm not able to locate the database,
even though I navigate to the appropriate folder where the SQL dB is
located. Am I missing some sort of setting in the SQL db??

No. You don't have to navigate in the file system to search for something.
You have to create a File Data Source. For this purpose, you have to select
a driver to use for connection. The driver to use is named "SQL Server".
WARNING!!! DO NOT USE "SQL Native" driver, 'cause it's not supported from
Access project connection. You can you SNAC in an ADO recordset connection,
but not else more.
Once you selected the driver, the wizard asks for the SQL instance to use:
here you will select the named instance of SQL Express, e.g. YOURMACHINENAME\SQLExpress.
Now the wizard will ask you the login type: use SQL login, e.g. sa. Type
the corresponding password.
If all was ok, the wizard will ask you which tables you want to link to your
MDB.
That's all.