Unanswered: Trying to move Access DB to SQL Server, keeping the forms
I'm trying to convert an existing Access database so that
the tables are held on the central SQL server, but keep
the forms that are in the Access database currently.
My idea was to export the tables over to SQL Server, which
I have done. Then delete the local tables currently stored
by Access, insert the linked tables via. an ODBC
connection, so that the form sees the tables as if they
were still there, but that these would connect to SQL
However, I had used the Upsizing wizard before when moving
a test database over to SQL Server. In the current Access
database this option is grayed out.
I managed to get the tables exported by right clicking on
the table name and choosing export to an ODBC connection.
Again, this worked fine and the tables (and their data)
are safely on SQL Server. However, the next stage of my
idea was to then delete the tables locally. The delete
option is also grayed out.
The current Access database was produced by an external
contractor before I joined a couple of years ago, so I
have no idea what he could have set on the database to
possibly limit the current users' actions. Myself not
knowing a whole lot about Access means I've got very few
ideas about what I could tweak.
If anyone has any idea as to why the options might be
grayed, or any suggestions about how to do this I'd really
appreciate it. It's been bugging me for a few days now!
I think that the delete option in your Access db is grayed out because the tables you see in access are the tables stored in SQL server. I don't think that you needed to create an ODBC kink either. This is all done by the upsizing wizard.
I haven't exported any databases yet. I've got the existing tables exported, but I'm trying to delete the tables from the current database and then insert the linked ones so that the data will be coming from SQL server rather than the .mdb.
I have a feeling its to do with the security settings that someone's put on it to prevent users from modifying the structure.
I'll try and find out the password, or alternatively, I'll have to try and work around the problem!