February 18, 2008 at 8:45 am
Friends,
I own MS Access DB and another group is trying to migrate to SQL Server and for this process they are asking me to provide .LDF file. I have never heard of this file in MS Access as I am very familiar in SQL Server (Transactional Log Data File). Can you please clarify here if I am not losing my mind? I appreciate it very much.
February 18, 2008 at 10:00 am
My limited knowledge of access tells me that there is no ldf file...just an .mdb i am not an expert in access though
Gethyn Elliswww.gethynellis.com
February 18, 2008 at 10:23 am
Don't believe there is an ldf. I know that newer versions of Access can install with some storage mechanism like SQL Server Express, but I'm not sure there's an LDF.
February 18, 2008 at 11:21 am
There is no ldf on Access, to migrate an application from access to SQL, you use the Upsize Wizard, if this is not available then you just create the database in SQL and transfer the data from Access to SQL, it no different then spliting access to create an interface and a database.
let me know if I can help you.
"We never plan to Fail, We just fail to plan":)
February 18, 2008 at 1:42 pm
Thank you all for responding with answers. I did send an email to the *other group* that no such thing exist in MS Access. Have a great day!!
-Sanjeev
February 18, 2008 at 2:59 pm
It strikes me that the other group doing the migration is getting an SQL Server error and they know little to nothing about SQL Server. If you are expected to support this after the migration, I would get in there quick and make sure things are done properly.
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
February 18, 2008 at 3:04 pm
Thanks Scott....and I hear you. First of all, they did not ask me for the migration team which is fine. Later today, they pinged me to join them for some brain storming session regarding migration. Hopefully, we can have a positive experience with migration and ultimately a working database. Thanks for reading my question and comments. I appreciate it very much. You guys are great and I enjoy reading this blog every day and learn from it.
February 19, 2008 at 12:11 am
Hi.
You already have the answer. There is no 'ldf" file associated with Access. Here are a few additonal thoughts.
Access can link to SQL Server tables via ODBC. This would allow you to work directly with those tables from your Access Front End, just as you would with any other linked tables. Well, not exactly the same, of course, but very, very close to the same.
If you want to use the built-in upsizing wizard in Access, you can create the tables in SQL Server with it. You'll need to provide a DSN through which to manage the ODBC connection.
You can import the mdb tables from within SQL Server. I am more familiar with SS 2000, but there is a mechanism in 2005 which works very similarly. I have only used it once, so consider this an overview, not step-by-step.
Make sure the mdb is located in a share where you can get to it from within SSMS.
Create the database in SQL Server into which you want to import the Access tables.
Right click the database and select "Tasks--> Import".
That will open a wizard which will walk you through the rest of the steps. For example, you'll be asked to specify the type of datasource (MS Access) and be given a chance to browse to the mdb you want to import.
This route should eliminate the need to create a DSN since you're not linking with ODBC.
HTH
George
February 19, 2008 at 5:47 am
Guys,
Can I correct you: there is an LDF file associated with MS Access. It is a Locking file and is created when you open an access mdb file; when the last user of the mdb file closes the MDB the file is destroyed.
It is not, therefore, needed for a migration but is a reason why a user of an MDB file needs write/create file permisions on the folder holding an MDB file (only if they are the "first" user of the MDB) and can cause problems when you Link to a mdb file from SQL as the SQL Agent User will probably not have write permision to the folder.
Glad to see you are now part of the migrtion team!!
/Richard
February 19, 2008 at 6:14 am
The .mdw file is the detailed permissions file that is only established if you are not using the default security in Access. The .ldb file is a temporary file that is created when multiple users are accessing the same Access database, and deleted after the last user is finished. It is not something that would be of use to someone migrating to SQL Server. A agree that .ldf is not a file that Access creates/uses.
February 19, 2008 at 7:11 am
duh, of course you spotted my foolishness i meant ldb not ldf.
Apologies all round
/Richard
February 19, 2008 at 7:15 am
Friends,
Thanks for your invaluable time and great responses. I appreciate all of you.
-Sanjeev
February 19, 2008 at 7:33 am
I wouldn't be suprised that these wanabe DBA are trying to attach the Access database directly into SQL Server. But hey, I'm not here to judge anyone.
A quick and easy way is to use DTS (from SQL 7 or 2000) or SSIS (from SQL 2005) to migrate the data into SQL Server. Be carefull to set the right datatype for all columns of the new SQL table.
Good luck.
D. Couturier
DBA
February 19, 2008 at 8:04 am
It is possible, as I said before, to use Access as the Front End to a SQL Server Back End. I do it all the time when I have large datasets that don't behave well in Access. In that scenario, the interface resides in Access, the tables, of course, reside in SS.
That is NOT the same as linking to Acccess from SQL Server, though. If someone WANTED to attach an Access mdb to a SQL Server db for some reason, I suppose that could be done, but I don't see the point, except perhaps for the purpose of migrating the data, of pulling in periodic updates.
BTW, when we talk about MDWs and LDBs, we're talking about all Access versions before the current version, which changed a number of things, including the file format and security.
February 19, 2008 at 8:21 am
Actually, the default mdw is always there, whether or not a particular database has been secured using user level security or not. If you have Access installed, you'll find "system.mdw" somewhere on your hard drive. It is invoked for every unsecured mdb with a Username of "Admin" and a null password.
In other words, by default, whenever you open an unsecured mdb, you are signing in as "Admin", no password.
The ldb file is also created for each mdb whenever the first user opens the mdb. As other users open the same mdb, their user information is added to the same ldb file. As users close their copies of the mdb, that is supposed to be logged in the ldb file as well. The last user to close the mdb also deletes the current ldb file associated with it.
That's the reason everyone who uses a shared mdb must have read/write permissions on the folder in which the mdb resides--they have to be able to create, modify and delete the ldb as appropriate.
As has already been pointed out, this should not have an impacat on linking Access to SQL Server, or with migrating data from Access to SS.
However, it does bring up an interesting issue which I have never thought about, which is linking to an Access mdb from within SQL Server for the purpose of retrieving data. I have not yet looked into whether that casuses an ldb file to be created for the SQL Server connection. I suspect not, but I am going to check it out as soon as I get a chance.
===============
Edit.
Linking to the mdb from with SQL Server to import data from the tables in the mdb into SS does indeed cause the ldb file to be created in the folder where the mdb resides. That may have implications for an import launched from within SQL Server.
My apologies if my previous inaccurate comments were misleading.
GRH
============================
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply