November 8, 2010 at 6:21 am
Hi all
I am not familiar with access so I have just started to read up on this. But since time is a factor(as allways) I figured this forum would be a good starting point.
My case is that I just got a request to make an automated procedure that transfer data from 6 tables stored on msql sql 2005 into access 2003.
one example
Customer table in ms sql 2005 has about 20 000 rows
Customer number, nchar(10)
Customer name, nchar(40)
Customer city, nchar(20)
Now I need to make the same in access, but I also need it to be runable. So that access can update changes once a day(several times if possible)
Can anyone help me with some starting ideas?
Thanks in advance for your time and help
November 8, 2010 at 12:31 pm
Hi,
By clicking on the External Data tab in Access, you can either create a copy of the SQL Server table in your access database, or you can create a linked table where changes made in Access are replicated in the SQL Server table (and vice versa).
External Data -> ODBC
Does that help get you started?
November 9, 2010 at 4:25 am
Hi
I can create a copy of the table yes, but I can not figure out how it is linked.
A copy is static, and I need a dynamic one. Linking therefor sounds like my option, but I can not figure out how.
November 9, 2010 at 5:34 am
Dan-Ketil Jakobsen (11/9/2010)
HiA copy is static, and I need a dynamic one. Linking therefor sounds like my option, but I can not figure out how.
Right.
I only have Access 2007, but the approach is basically the same.
"External Data" -> ODBC Database -> A box pops up where you can choose to copy the table or create a linked table.
Choose "Link to the data source by creating a linked table".
In the next message box ("Select Datasource") you will click on "new" and select SQL Server as your connection.
Keep following the wizard - it'll ask you if you want to name the connection, the name of the server, and the database you want to connect to.
Once you finish the wizard, a table will be created that will show the data from your SQL Server table. Updates will be replicated whether you make them in SQL Server or in Access.
If my steps aren't clear, look in Access help for Linked tables.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply