ODBC with Access 97 and 2000 2

[ruthcali]

I have both Access 97 and 2000 on my computer.

Every morning, i open up my database, delete a table and then use ODBC to import a fresh table into the database.

When i use Acc97, a field called proj_id imports as a Text field. Good.

When i use Acc2000, doing the same import method above, the same field imports as a Number field. Bad.

Does anyone know why this happens?

Thanks,

 

[Tranman]

Ruth,
I had a similar problem. Solved it by changing the procedure. Instead of blowing away the Access table and re-creating it with an import, I just deleted all of the rows and then did an append query from a linked external data source. That way the basic table structure was preserved.

BTW, if you're importing from EXCEL, the import decides whether the data is text or number, based upon the value in the first row of data (row 2, if you tell it that the first row contains column names). Another reason to preserve your table structure, and just delete and replace rows.

Tranman

 

[ruthcali]

Tranman,

thanks for writing. your way sounds good, but what if a new field is added to the table that I import? Will i have to check for that each time in order to update my append query?

And the table i import is big. it has 101 fields with 4000 records. I don't want to have to write an append query for 101 fields.

Will an easier way be to just use code to change the data type from Number to Text?

But, i'm just wondering why Acc2000 imports the field as a Number and Acc97 imports as a Text. Baffling, isn't it?

Because of that, and my SendObject not working with Acc2000, i want to stay with Access97. (I read that installing Service Pack 3 will fix the sendObject failure, but my IT guy is having troubles getting the SP-3 installed since it's not part of our corporate image).

 

[Tranman]

Actually, the append query is pretty simple:
Insert into tbl1 select tbl2.* from tbl2;

If someone adds a column to table2, the append query will blow up, and you get an error message telling you what column was a problem. Then you would just need to add it to your local table and rerun the append query.

As to changing the data type, I'm not certain you can do that...I know that you can't in Oracle, and I'm pretty certain that if you try some trick like
currentdb.tabledefs("tbl1&quot.fields("myfield&quot.type = dbchar

or whatever, that it will error out because the field has already been added to the fields collection with the original type assigned.

I think that the puzzling thing is due to a change in the ODBC driver you're using and not Access2K.

Tranman

 

[Hap007]

Why not Link your New table (The one you import each morning) to your database. Then, each morning, run a delete query on the target table and then second, do an append query for all data.

Steps:
1) Delete query on all records
2) Append Query from the linked ODBC Source to your target table.

Now that I have offered a solution, I have a question:
Why not just link to the good data and use that table all the time? You probably have a good reason, but I just thought I would ask.

Hope this helps...

 

[ruthcali]

Sorry for the delay in writing back.

Tranman,
Thanks for the append query. you're right. that's easy!

I don't think it's the ODBC driver, because i am using one computer that has both Acc97 and Acc2000. So they both use the same ODBC driver, right? On the Acc97 database, the field imports as a Text, but on the Acc2000, the same field imports as a Number.

Changing data types from Number to Text can be accomplished with the following code which i got from DougP:

Sub AlterFieldType(TblName As String, FieldName As String, NewDataType As String)
Dim db As Database
Dim qdf As QueryDef
Set db = CurrentDb()

' Create a dummy QueryDef object.
Set qdf = db.CreateQueryDef("", "Select * from Table1&quot

' Add a temporary field to the table.
qdf.SQL = "ALTER TABLE [" & TblName & "] ADD COLUMN AlterTempField " & NewDataType
qdf.Execute

' Copy the data from old field into the new field.
qdf.SQL = "UPDATE DISTINCTROW [" & TblName & "] SET AlterTempField = [" & FieldName & "]"
qdf.Execute

' Delete the old field.
qdf.SQL = "ALTER TABLE [" & TblName & "] DROP COLUMN [" & FieldName & "]"
qdf.Execute

' Rename the temporary field to the old field's name.
db.TableDefs("[" & TblName & "]&quot.Fields("AlterTempField&quot.Name = FieldName
' Clean up.
End Sub


to call sub

AlterFieldType "Table1", "Employee", "LONG"

Hap007,
Thanks for writing. When i first created the database, we were asked not to link to the tables because it would slow down the network. But the way you and Tranman mention about deleting and appending is a good way. I'm just worried about new fields being added to the table since i think new fields are added quite often.

 

[Tranman]

Ruth,
Regarding ODBC drivers...there is nothing that says that two databases, or even two tables in the same database are using the same driver. At one point when I was troubleshooting my data type problem, I had the same table linked into an Access 2K database four times, using four different ODBC drivers.

I'm kind of like Hap007. I tried to address your import problem because I assumed that for some reason, you had to import instead of link. I think I'd try linking and see what effect it has on your network.

BTW, what is your data source? Oracle/SQL/Excel???

Tranman

 

[ruthcali]

Thanks for writing,

These are the steps i did to get the SQL server:

-I have Windows 2000. I went to the Control Panel, Administrative Tools, Data Sources (ODBC). Then i clicked the System DSN tab and clicked Add. I choose SQL Server (version 3.70.09.61) (file SQLSRV32.DLL) (date 07/22/02). Then i clicked Finish.

-Type the name in the Name box and the server name in the server box.

-Click on the radio button 'With SQL Server authentication using a login ID and password entered by the user.'

-Click the Client Configuration button and click on 'TCP/IP' on the left side of the window under Network Libraries and then click OK.

-Type the Login ID and password.

-Click the Next, click Next, click Finish.

Then in my database, i click File, Get External Data, Import. In the FILES OF TYPE drop down box, i select ODBC databases.

--Then I click on the Machine Data source tab and highlight the driver with the name that i entered in the first step and click OK.

Since i went to the Control Panel and did the first steps above only once, doesn't that mean that i am only using SQL (version 3.70.09.61) everytime i use OBDC to get my table?

How do the drivers change? How can i tell which driver i am using? Is there a way to fix it so importing w/access 2000 is the same as importing with access97?

Thanks,
Ruth

 

[Tranman]

Hi Ruth,
Thanks for responding. If you are using SQL Server, then chances are that the only available ODBC driver is from Microsoft.

This also probably means that both Access 97 and Access 2K on your machine are using the same Driver (unless you created another DSN previously that is still lurking on your machine and being used by the 97 database.

ODBC drivers get changed when the MDAC (Microsoft Data Access Components) is(are) installed. This can happen during Operating System installations, software installations (like Office or Visual Studio), or can be run in a stand-alone installation of the MDAC setup program which has been downloaded from Microsoft.

At this point, I would probably look in the knowledge base for references to importing problems.

Good luck,
Paul

 

[ruthcali]

Thanks Paul,

It's strange that Acc2000 imports as a Number and Acc97 imports as a Text, if i'm using the same driver for both.

Oh well. i searched around in the Microsoft Knowledge base, but i haven't found anything yet.

Thanks for writing though.
if i find something, i'll post it here.

 

[Rick39]

Have you considered CStr() on the field in question to force the data to string type?

 

[ruthcali]

Rick,

Cstr is a good idea, but i need the data type changed in the table since i have many relationships with that table.

If i need to, i can use the code above to change the data type from Number to Text after i import the table.

But, my question is why, on one computer, Access97 imports the field as a Text and Access2000 imports the same field as a Number.

And it's not just my computer. i have colleagues who import the same table. Using Acc97 imports as a Text and Acc200 imports as a Number.

 

[Hap007]

Ruth,

You are wondering why Access converts differently, well here is my unproven thoughts.

In Access 2.0 you had to describe exacly how and what the import fields were layed out.

Then Access97 tried to help you by reviewing and trying to make the correct choices for you.

At this point, I always used Access 2.0 for importing since as a programmer, I new what I wanted and I did not need Access(97) to make those decisions for me.

Now, I am guessing that Access 2000 is making even more decisions for you.

How are those decisions made:
Well, from my past experience, access reads a certain number of records into memory, examines the fields in the samples processed and makes a choice. Note: This is just a guess, but if Access97 returns 10 rows and the field it is examining only has numeric data in the fields examined, then it decides it is a number field. But had it seen in one of those fields an alpha numeric character, then it would have set the field as text.

What does this mean, perhaps Access97 is testing a different set of records or a smaller set and the test only sees numbers in Access 2000. I am guessing that the records tested are randomly read.

Again, I am just guessing here.

Hope this Helps,
Hap Access Add-on developer

http://www.fcs-software.com

-

 

[ruthcali]

hap,
thanks for writing. your reasoning makes sense.

I've run into troubles before while trying to import data from excel. i got around that by imporing the header fields, that way i force Access to keep my fields as Text instead of Number.

But when i import using ODBC, i have no control of what gets imported. i can only pick the name of the table to import. oh well.

i guess i'll have to run the code above to change the data type after importing. Either that, or change all my other tables to Number, then change all the code to reflect that field as a number.... uggh.

 

[christywarner]

Hi,

I'm trying to convert my 2000 db to 97,
but the import function doesn't seem to
work in 97. (File / Get External Data / Import).
How do you automate importing files in 97?

Thanks!
Christy.

 

[Tranman]

CW,
If all you're trying to do is convert a 2000 database to '97, why not just save it as a previous version?

In XP, it's like: Tools|Database Utilities|Convert Database|To Access 97 Format.

All of the Access versions have this functionality, but it seems to me like it was implemented a little differently in earlier versions.

Tranman