Split Database Access FE and SQL Server BE

[accessNator]

I am currently working on an application where I am using MS access as my FE and working with linked tables to SQL server BE. I also have a few temp tables within Access that are not linked but needed.

1. What are my steps into making this into a SPLIT DB? I am assume the BE will have my linked tables from SQL and the FE will have my temp tables and forms. Is this correct?
2. Once I split it, then I can make an .ACCDE file for a Mulit User environment, is this correct?

What other steps should I be made aware of?

Thanks,

 

[pbaldy]

It's been a while, but you can use the upsizing wizard in Access, SSMA in SQL Server, and perhaps other methods to push the tables to SQL Server. SQL Server would be the BE, there would not be a BE that had the SS linked tables in it. Yes, the FE would keep the temp tables, forms, reports, etc. Yes you can make an accde to distribute to users (and you keep the accdb for development).

 

[Galaxiom]

It is a better practice to place the Temp tables in a separate local database outside of the FE.

Code can be included in the FE to generate the temp database on demand.

 

[accessNator]

It's been a while, but you can use the upsizing wizard in Access, SSMA in SQL Server, and perhaps other methods to push the tables to SQL Server. SQL Server would be the BE, there would not be a BE that had the SS linked tables in it. Yes, the FE would keep the temp tables, forms, reports, etc. Yes you can make an accde to distribute to users (and you keep the accdb for development).

In my Access App, I already have the sql tables linked via ODBC.
So I really dont have to worry having a BE access DB correct? I can simply just create the ACCDE, correct?

Thanks,

 

[accessNator]

It is a better practice to place the Temp tables in a separate local database outside of the FE.

Code can be included in the FE to generate the temp database on demand.

I thought about that too, but I needed the TEMP tables within Access. I know its better practice from what I read but I needed those temp tables so my continuous forms have a local place temporarily. Maybe I'll revisit the code to make it more efficient. I know it may create bloat, but I have it set to compact when in use.

Thanks for your reply.

 

[pbaldy]

Sorry, I misunderstood. If you already have the SS tables linked, you already have a split db. Yes, you should be able to create the accde now.

 

[Galaxiom]

In my Access App, I already have the sql tables linked via ODBC.
So I really dont have to worry having a BE access DB correct? I can simply just create the ACCDE, correct?

Yes. Though I would seriously consider putting the Temp tables into what I call a Side End. If you search the forum for that term you can see previous discussions about the benefits.

 

[Galaxiom]

I thought about that too, but I needed the TEMP tables within Access.

The temp tables in a Side End are linked like any Access Backend.

It avoids the bloating of the FE.

 

[accessNator]

Yes. Though I would seriously consider putting the Temp tables into what I call a Side End. If you search the forum for that term you can see previous discussions about the benefits.

Thanks for the term. I will look it up.