My Colleague has been developing a access database and accompanying queries & forms, and we wish to migrate this to Microsoft SQL using the upsizing wizard, leaving access as a front end.
I have a SQL server up and running and we have successfully used the upsizing wizard, ticking the option of creating a new database in access 2010. It all seemed to go very smoothly, making local copies of the tables, and linked copies. Forms and queries still work really well. We then deleted the local tables.
However, ultimately we would like the database to be developed by several people, using different front ends.
We have come across a problem where if I create a new table in, say, vb.net then this new table is not shown in access. I can't find any way of updating the linked tables in access to include the new one.
We have tried clicking the 'ODBC data' in 'external data' to add a link to the new table, but in order to do this it seems we need to create a file based DSN link to the database. Surely there is another way, given that the upsizing wizard has created a link that is already working fine.
Alternatively, we would be happy always creating tables in access, but if you create a new table in access, it is only a local table, and I similarly can't find a way of updating the 'upsizing' to include this new table.
I don't have a very big understanding of how access has automatically created the connection, so I am missing something. Similarly I don't have a big understanding of ODBC connections & DSN file links. Sorry.
Thanks in advance for you help.