Period(mdb) vs Underscore(accdb)

[thr33xx]

I am in the process of migrating over a database from 2002 to 2010. One of the things I noticed different with 2010, is that Access will name tables using underscores e.g. dbo_asys_Employee. Whereas 2002 will use periods e.g. dbo.asys.Employee.

What are the implications and issues that may arise during the migration of the database?

The method I am using to migrate to 2010 is exporting all objects over to a blank database. The reason for this is because when attempting to convert the original mdb database, the option to save as current default or accdb under the save and publish selection was greyed out and unavailable.

Would appreciate all insight and anyone who has related experiences.

Thanks

 

[the_net_2.0]

more than likely some of your macros won't work or might show some issues. Macro format was a big change in 2010 and I believe some of the internal structure and/or code changed too. But the only way you'll know what has changed and what hasn't, is to run through it yourself and cover all possibilities. There's no other way to do it, if you want to be completely sure, obviously. You might also run into coding issues, as syntax has changed from 2002 to 2010, but it's rare to see. But it's mentionable because it has happened. Those little changes aren't published by MS either (at least not anywhere visible) so it's up to you to find out what they are. But else is new with that ridiculous company, right?

the . and _ , I have no idea. Did one character change to the other when you imported tables? are these characters PART of your table names? You weren't clear on that.

 

[thr33xx]

Sorry, these tables are being pulled from a SQL server. In Access 2002, they are displayed with periods, and in Access 2010, they are displayed with an underscore.

 

[the_net_2.0]

Sorry, these tables are being pulled from a SQL server. In Access 2002, they are displayed with periods, and in Access 2010, they are displayed with an underscore.

well then it's related to SQL server I would think. 2002 is reading the strings differently than 2010 is. I don't think there is anyway to figure out why, because you'd have to read the internal code of Access, which I believe is either C or C#.

You might be able to find an article about that though, on SQL server websites, but probably not here.

 

[Banana]

I think there's something else going on, because I'm very fairly sure that periods was never allowed in an Access object name.

I have a strong hunch that your 2002 file is actually an ADP file and you imported the objects into an ACCDB file. An ADP file interfaces more directly with SQL Server and thus would probably use periods to identify database.schema.object (though I don't think ADP ever allowed more than one database so it has to be just schema.object).

Can you confirm if this is the case?

If it is indeed the case, then you'll find that the underscore is insignificant in terms of linking since during the linking, SourceTableName property will contain the correctly qualified name but for compatibility with Access' database engine, use Name containing underscore. Of course that doesn't mean your VBA code will necessarily work, especially if your VBA code is assuming a CurrentProject.Connection (which would be different in ACCDB file and therefore assume an Jet-compatible syntax so it'd break with T-SQL syntax).

I hope that helps... Let me know if I'm off base.

 

[the_net_2.0]

Banana,

Did they do away with ADP's in 2007 or 2010? Wasn't it one of those two?

 

[Banana]

No. It's still supported in all versions. Just harder to create a new ADP than in previous version (there's no longer a obvious menu option as there was in 2003 but it's still there nonetheless). I'd also not recommend creating/starting a new ADP at this point. There's really nothing new for ADP since 2003 though it continues to be supported.

As a matter of fact, I do have a client using ADP on 2007 and last time I checked, it works on 2010 though that's not supported in production just yet.