Questions about upsizing

[jrhbright]

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.

[ajetrumpet]

havent used the upsizing wizard in a while.

but how did you come to the conclusion that a file DSN will solve the issue?? I don't see how that would play into this.

what it sounds like though, is that the sync is only a one time thing when you use the upsizing wizard. as in, changes are not reflected in "real time" status. is that what's going on??

and you are using vb.net as your web scripting source??

[jrhbright]

Thanks for your reply

"what it sounds like though, is that the sync is only a one time thing when you use the upsizing wizard. as in, changes are not reflected in "real time" status. is that what's going on??"

You're right, and I'm not too surprised that the changes are not reflected in 'real time', but I'd at least like to find a way of forcing it to update to include links to the new tables, or add the new tables manually.

"but how did you come to the conclusion that a file DSN will solve the issue?? I don't see how that would play into this."

Because, when trying to add a link to the new tables manually from access, the only way to do this seems to be with a DSN file.

"and you are using vb.net as your web scripting source??"

we are using vb.net as a second front end, but not for web scripting. I don't think this matters?? I was just saying that if you create another table on the SQL database on anything other than access, then it doesn't get updated in access.

[ajetrumpet]

so with the file DSN, are you linking your tables with that through code that you are writing in vba when the db is opened?? (thats the only way I've ever used one)

see if there is anything to be found here: http://www.google.com/#sclient=psy&h...1&pf=p&pdl=300

the first one seems to be up your alley.

[NTC]

depending on how I interpret your post, along with the q/a so far; it appears your ongoing development is such that a developer would create a new table locally, work with it awhile, and then at some point need to move it to the common back end.....

if so then this is readily possible in the Access environment; and so you could temporarily return to a pure Access environment and then re-upsize later. Or alternatively you would need to review sqlserver methods of importing a table from access (not upsizing).

hope it helps.

[NTC]

made a typo - - that should be ; review sqlserver method to import a table (from your vb.net file) ... not being an sqlserver manager I don't know about this....