Link to SQL Server with invalid field names 1

[Fubear]

I am writing an Access Database that links into an SQL server using ODBC links. Some of the tables on the server have invalid field names, EG: "Reg.No."

These tables refuse to be linked to access because of the naming convention. Though my limited access to the server, I can run a DTS module that will convert the field names and will export the data into my access database, but this is a slow process (35,000+ records) and creates a copy of the table rather than an ODBC link to the original.

Having several off site tables in the database is something i want to avoid at all costs. I'm no SQL Server admin, I just have read access to the relevant tables to my project.

Is there any easy way to achieve this?

 

[cmmrfrds]

Can you explain how you need to use the data from the sql server tables in your access app. Record source for Form or Report? Query only? Do you need to link (join) sql server tables to local Access tables?

What version of Access?

 

[Fubear]

Sure - currently i need to link the tables to local access tables for display and update (only the local table) in a form. I am having problems with doing this at the moment as well, i have a thread on that here:

thread702-397666

 

[cmmrfrds]

Really need to know what version of access since there is more flexibility with Access 2000 and above. Also, can you do work on sql server or are on good terms with the dba. There are possible solution on the sql server side.

On the other thread, how many of the 40k records do you really need in the app at any given time? How many records in the local table that you are joining to the sql server tables.

Have you used pass through queries in the past?

Are you familiar with ADO?

 

[Fubear]

I'm using access 2000, I was pretty much thrown in the deep end when it comes to access, I have gotten to grips with the basics but there is still a lot i dont know.

The way i need the data to link is such:

Table1 (40K records)
Field1 ---Table2->Table3
Field2(K)-->Table4
" -->Table5

Tables 1-4 are the ODBC tables, while Table5 is the local table. Tables 1-4 select relevant data from the SQL server, and table 5 is for the user to enter their own comments and information. No more than one record in any relationship should match, but there may be a case where records do not match, so the arrow type joins are required. I cannot afford to have records from table 1 being skipped.

Out of the 40K records, only about 20K are required, and there is a form that will filter a sub-datasheet containing this data for the user to select relevant records, I think the max number of records a user can filter at once is ~9k.

As for the local table, its empty at the moment. I need to figure out a method of having it generate a new record when a user tries to enter data into a field for a record that does not have a record for it already.
---

I am on good terms with the DBA, he is very busy though and it usually takes him 3-4 days to get around to even a simple request for me. I have a limited acess login to the SQL server, and I have MS-SQL server instaled on my machine so i may use the enterprise manager to perform simple tasks.

 

[Fubear]

Im trying to convert a local database to use the central SQL server. Previously it took imports from the SQL server through Excel and only used one table, the updates were all manual by me.

I am trying to automate it with the main server now, and eventaully even table 5 will be implemented into the server. Tables 3 and 4 give me error mesages while trying to import as they both have field names containing reserved words or periods.

 

[cmmrfrds]

Since you are using Access 2000 and SQL Server, this is the perfect time to use an Access Project. With a project you are connected directly to the sql server database and can treat the sql server table in your application much like you treat local access tables in an MDB. So, I suggest reading up on Access Project (it is relatively easy) and going that route. It will make things easier for you and your DBA once you become familiar with Access Projects (adp). Definitely take the time it will be worth it.

Right now more than likely the ODBC driver is kicking out the fields you are having trouble with, or possibly Jet. In either case a direct connection with an adp will bypass both.

Also, you can use Query Analyzer to write and test your queries directly in sql server. It can be a separate executeable (isqlw.exe) or invoked from enterprise manager.

 

[Fubear]

thanks a lot for the useful help - I suggested to the DBA that he use a view, and he slapped his head and said "Of course". So i got the data working now.

I will look into using access projects, and im trying to get more than simply read access to the SQL server so i can code tighter integration through views and procedures (we havea very slow network).

 

[cmmrfrds]

That was a good idea to use a View to get around the invalid field names. It looks like you are on the right track with Views and Stored Procedures.