Question Help migrating access 2010 database backend to ... something else (1 Viewer)

[TiagoDM]

I have that problem. I'm a begginer with some knowlegde in vba. I want to move the backend of my access db into something that will provide speed and security. I have almost the whole db written in vba code to do this and that ...

1 - any *simple* way to move it to ... somewhere else?
2 - if I move ONLY the tables will that provide speed computing record information (on queries, knowing that the querie will be computed on the frontend and not in a server on the backend)?
3 - if answer to 2 is NO, then will I have to rewrite the queries in SQL format (or something else)? (I have about 120 access queries ... plus 20+- written in vba)
4 - will the new backend platform provide compatibility with multivaluedfields and attachements? (I have tons of tables' fields with them)


I'm sorry.. there are many questions and I have been thinking about this for a long time. The idea was to move tables only, but if that doesn't help with speed, than maybe it will be better how it is now. I have to make a decision. If I have to rewrite the whole db, that will take me over a year for sure. I was avoiding doing that, but if i have to .... Do you have any ideas? Thank you.

Tiago

 

[DavidAtWork]

Is your database currently split FE/BE, if so what is the BE file is it an Access file or something else such as SQLserver.
If it is split and the BE file is Access then to start with make a copies of your front end & back end and then copy the BEcopy file to your new destination and try to relink the copyFE to the new destination BE using the linked table manager and do some testing to make sure all functionality is working ok.

If your database is not yet split, then make a copy of your database and try the database Utilities to split it.

If your database is split with a SQLserver BE, then this will be slightly more complicated in that we need to look at how the current BE is connected whether by a DSN ODBC or using connection strings
David

 

[TiagoDM]

Is your database currently split FE/BE, if so what is the BE file is it an Access file or something else such as SQLserver.
If it is split and the BE file is Access then to start with make a copies of your front end & back end and then copy the BEcopy file to your new destination and try to relink the copyFE to the new destination BE using the linked table manager and do some testing to make sure all functionality is working ok.

If your database is not yet split, then make a copy of your database and try the database Utilities to split it.

If your database is split with a SQLserver BE, then this will be slightly more complicated in that we need to look at how the current BE is connected whether by a DSN ODBC or using connection strings
David

Its already split in access be\fe i share the tables in windows accbd shared files and link to another accdb file (frontend). What i need 2 do is to know if i can or how i can link those shared tables (with multivalued fields and attachments) to a faster, secure platform like Sql server 2008, 2012...? sharepoint server .. ? Something else..? ... or is better i give up and strt to write a new database from the scratch (which will take forever .. i have about 500+ forms, reports queries and tables... some with 80000+ records). So, whats the best solution? Any ideas? Thanks

Thanks

 

[Pat Hartman]

Multi-value fields CANNOT be converted to SQL Server. You will have to recreate them with properly defined tables and you will need to change your forms/reports to replace the listbox with a subform/subreport.

500 forms sounds seriously excessive. You should pay someone to look at the database and give you some advice regarding whether it is properly normalized and whether you really need 500 forms.

I've had applications with millions of rows that worked fine as Jet tables but most that get that large I convert to SQL Server. Given that I always build my applications with the potential to upsize, it rarely takes more than an hour to actually do it when the time comes. However, if you designed the forms in the Access "single developer, monolithic database" style, you will have some real work to do to get to an efficient client/server configuration.