VBA problem - create link table that has dot in its column name

[colintis]

Hi, I wrote a VBA code to create ODBC link table from SQL server. The code just works fine to most table exist in the SQL server database.

However, one of the table's column name has a dot (e.g. [Payment Freq. New]), which result unable to create link table. Below is the code that creates link tables, is there any solution to include the dot as part of the column name? Thanks.

DoCmd.TransferDatabase acLink, "ODBC Database", <Connection String>, acTable, <TblName>, <TblName>

[NeoPa]

You don't say what happens when you run this code (Other than simply that it fails).

I can only suggest you try to make the link manually and see what results you get. It may fix the name automatically by replacing the dot with an underscore or something. Otherwise, we need more details in the question.

[colintis]

When I run the code, it will populate a ODBC link table into the table list. But when I run the code to this particular table, no error has shown, and the link table is not populate into the table list. The reason I know it involves with the dot within a column name, is I tried creating a pass-through query to open the table, when I execute the query it ask me to enter value to this column. The Access program itself might have reckon the field name as <table>.<field> instead of a single field name.

I'll need some time to build the table manually for the try out, as the table structure itself is large. Can I program some code that read the column names of the table, so I can build the table with that in VBA?

[sierra7]

Hi
First I would never use a dot in a field name, same as I would never put my head in a lion's mouth! But I take it was not your choice. If you put [Square Brackets] around a fieldname it allows you to include spaces; I dont know how well it works for other characters.

Second Post: If your table exists in SQL Server then you can generate a script (from SQL Server Management Studio) to create the table. Right click on the table in Studio then select 'Script Table as > CREATE To' and give a file name.

You will have to modify this script before you can paste into Access but at least all your field names will be included and spelt (spelled?) correctly.

I agree with NeoPa that we need more info on what you are trying to do and why? First, it sound like you want to create a link (so you dont need the structure). Second, you seem to be wanting to create a local table?
S7

[NeoPa]

I would say it's a good guess that the problem is associated with the field name including a dot. I doubt you have control over the SQL setup, but even if you have no access to it yourself, I'm sure an SQL admin would be happy to get the info for you in the way S7 has indicated (Those scripts are actually stonkinly powerful).

PS. Spelled and spelt are both correct spellings of the past-participal of the verb To Spell :-)

[colintis]

Currently I'm told to create an Access database file to improve the performance of an Access Project file, because the database that is setup to be connect from the adp file contains hundreds of tables and other stuffs which is not needed.

What this access database will do is to list the necessary tables, then it will create a link table once the user select it. And to save time from refreshing data at start, these created link tables will be remove once the database is closed.

And for that dot in the column name....that's the client's requirement.....

What I might do is loop through the table to copy the records with DAO recordset. See if I can get the name correctly with the dot or not....

[NeoPa]

I'm sorry but I barely understand what you're saying here, let alone how it pertains to the question. I'm afraid the typos and misuse of grammar contribute to leaving me very few clues even to guess what you mean.

[sierra7]

Colin,
I'm still not clear what is going on. So far I understand that you have a SQL Server database with hundered of tables. OK?

I also understand that you company have an Access Data Project (adp) front end that is currently linked to ALL of the tables in the SQL backend. They believe this is causing a performance issue which can be resolved by reducing the number of tables to link to. Is that right?

Now the tricky bit. You have been tasked with writing another Access application which will maintain a list of the tables on the SQL server and then on start-up the adp can reference this (via a single link). The 'user' will select which tables are required from the SQL server and create those links.

This sort of makes sense if the 'user' is a 'user-group' who need access to specific functions, therefore only require data from specific tables. I would imagine these requirements would be hard coded rather than allowing users to select their own tables.

I could go on... but am I getting warm?

S7

[colintis]

Oh I do feel quite warm even the weather is hot down in Australia.

Yes, because the adp need at least 10 mins to reload every time when open.

It will be with the same interface as the adp has. The adp also contains a form that lists the required tables in the SQL backend. Difference is, the Access database file will not reload everything from the SQL backend on start-up.

Yes there is 'user-group' to limit access, but it only used to confirm the user identity for error logging. The stuffs other than the require tables are either RAW data or refine functions, which create the tables that those 'users' can check on.

In result, it reduce unnecessary data loads, any process in getting the data will only be done when required.

This works perfectly in the new Access Database, except handling tables that has a dot in some of the column name. The DoCmd code I paste initially does not create the link table, because it reckons characters before the dot as table name.

[sierra7]

Hi Col,
I tried putting a dot in one of my fieldnames in SQL Server. It was accepted and [square brackets] were put round it but I could NOT manually create a link to that table from Access 2010.

I've FIXED THIS by creating a view on my table where you have the option of putting an Alias on the offending field, obviously without the dot.

I was then able to link to the view from Access. In Access you can rename the view to whatever you want; I guess this will be the second <tableName> in the 'TransferDatabase' command in your first post.

NeoPa did ask in his first reply as to what happened when you tried to create the link manually. I've been presuming there was not a problem. If you had said there was an issue then we might have found a solution earlier.

On a seperate note, I've browsed Help on TransferDatabase and it says that you cannot use 'acLink' for an ADP. This is surprising because I would have thought you could ONLY use 'acLink' on an ADP

S7

[NeoPa]

@Colintis
I found your last post almost unintelligible. Please see my accompanying PM for more on this.

[colintis]

Hi all, apologies for my late reply and inappropriate use of grammars, I'll try my best to avoid that in future. I had read all replies back from start, and I apologize for missing out some contents.

I tried create the linked table manually, it shows an error: "Payment Freq. New" is not a valid name.

I had discussed the issue with my senior, it is not wise to continue further for solution. Because the table we are trying to link will rapidly drop and create with new data, creating additional views in SQL server is not permitted.

Through some discussions, my senior decided to leave the problem as it is, then do a manual export when required....

Many thanks for the efforts NeoPa and S7, for trying to help me with this issue. :)

[NeoPa]

Thanks for the response :-)

I won't progress this further in that case, but I would like to leave you with an idea that may pertain to your situation, but ignore it if it doesn't.

You mention that the table being linked to is likely to be regularly deleted and recreated. If the name is always the same then this ought not to have any negative impact. If, on the other hand, the new name is predictable then relinking tables is quite possible if you know what the name should be (Mary has an article on this which may prove instructive - Relinking ODBC Tables using VBA).

Of course, if the new name isn't predictable, or even if you don't like the idea, just move on to other problems as you were planning to do anyway.