Inserting records into MDB and SQL Server.

  • Thread starter Thread starter Drew
  • Start date Start date
D

Drew

I need to insert records from Access to a SQL Server. Not using DTS if
possible. My reason is this, We have 15 access databases, and they all are
linked together. I am upgrading Access to SQL Server, but while I am
working on the ASP for the SQL Server interfaces, people need to use the
Access Databases. I need to find a way to make sure that if people change
something in the Access DB, that it is also changed on the SQL Server.

It isn't a great use of resources, but these DBs are used everyday, all day.

Thanks in Advance,
Drew
 
Why not just convert the table from the mdb file to the sql server, and then
use a linked table to sql server?


I mean, the amount of work you will need to ensure that when a record is
updated in ms-access, and then trying to add a bunch of code to try and
send/update the same data on sql server is going to be quite hard. I suppose
if users were only ADDING data, then your problem might not be too hard, but
trying to maintain the edits between two tables will be quite hard. (if you
MUST do this, then check out the on-insert event of a form (there you could
add the record to sql server, and then check out the after update event
(there, you could update records on sql server).

However, the amount of code/testing to get the "insert" and the "update"
events working with sql server is going to be more work then just moving the
table to sql-server, and then linking to that table from ms-access. Why not
just move the table to sql server, and then use a linked table on ms-access?
After all, ms-access is NOT a database, but only the front end (you can use
JET with ms-access, you can use sql-server with ms-access...you HAVE a
choice here).

I mean, you are moving the data to sql server but keeping the forms, code,
reports etc in ms-access, and simply linking the tables to
sql-server...right?
(I assume this is your plan of migration, as sql server of course does not
let you build applications, or a interface like ms-access).

It likely going to be less work to move the table to sql server, and then
use a linked table in ms-access to that table on the server. Your users will
never know the difference, and can continue to use the mdb/mde file, but now
the data will reside as a table on sql server.
 
Back
Top