[RESOLVED] VB6 & Access question

[Aelanna]

I am using VB6 and Access 97. My question is, is it possible inside a VB program to change the field attributes of a table in an Access database? For example, can I change a text field to number or number to text or change the format of the field or other such things?

[DKenny]

Why would you ever want to change the DataType on a column as part of the normal operation of a database?

[RobDog888]

Yes, you can do it but you may create an error is the data thats in the column is no longer valid for the destination data type. Are you sure this is what you want to do? and if so then how do you plan on invoking it?

[Aelanna]

Well, if you can tell me another solution I'm all ears. Here's my problem. I have a table in a database with an ID field (Stable_ID). I want to use this same database for both an active server page on my web site and a VB program I'm writing, so I don't have to have a bunch of seperate databases. The web page makes no changes, all it does is display what's in the table on the web page.

When this field is set as Number, the web page no longer functions, but it works fine in the VB program. When I set it as Text, the web page works fine once more, but the VB program returns type mismatch errors.

So, I thought if I could tell my program to change the field to Number, make whatever changes I need to in the program, then set it back to Text when done, I would be able to use the program for making changes, and still be able to import the database onto the web site for display. But if there's another way to do it so the web page AND the program both work, that's fine with me.

[RobDog888]

No offense but that is not a good solution for many reasons. It would be best to fix the issue by casting the data type to a valid type for one of the uses so no physical changes will be needing to be done/saved.

Can you give more details on the issues for each problem?

[Aelanna]

Ok, if the field is set to type Number, the VB program works. If I set it to Text, VB gives me Runtime Error -2147217913 (80040e07): Data type mismatch in criteria expression. Which is the SQL statement:

SQL = "Select * from Horses WHERE Stable_ID = " & SSID(Combo1.ListIndex) & " ORDER BY Horse_Name"

Now, on the .asp page on the web site, if the field is set to text, everything works fine. If I set it to Number, then I get

Microsoft OLE DB Provider for ODBC Drivers error '80040e07'

[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.

/fhr/Stables/login_user.asp, line 72

With the SQL statement:

sql = "SELECT * FROM Stables INNER JOIN Horses ON Stables.Stable_ID = Horses.Stable_ID WHERE Stables.Stable_ID = '" & Num & "' ORDER BY Horses.Horse_Status, Horses.Horse_Year, Horses.Horse_Name"

So, apparently ASP likes Text and VB likes Number and I don't know how to make them speak the same language.

[RobDog888]

The issue is that your not using the same WHERE clause. In the Vb program one you have

"WHERE Stable_ID = " & SSID(Combo1.ListIndex) & " ORDER BY Horse_Name"
 
'And in the asp page you have...
WHERE Stables.Stable_ID = '" & Num & "' ORDER BY Horses.Horse_Status, Horses.Horse_Year, Horses.Horse_Name"

Note, the = sign on one has single quotes around the criteria value and the other where clause doesnt. This designates that one criteria is going to be a string or text value and the other is going to be numeric. If you change one of the where clauses criteria to no single quotes then they will work the same. If your adding records then the id should be numeric.

[Aelanna]

Well, I'll be darned!! I changed the asp page by taking out the quotes and changed the ID field to Number and it works!! Thank you!! I have had that stupid page set up that way for years now not knowing what was wrong with it, and now it works the way I originally wanted it to!

You must be sick of seeing my posts by now, I'm sorry I've monopolized so much of your time today, but I really appreciate all your help!

[RobDog888]

No worries as I spend almost all day and night on the forums.

I posted a reply in your other thread.

Ps, dont forget to 'Resolve' your thread.

[Aelanna]

Ummm... how do I 'Resolve' it? (Feeling stupid now)

Never mind, I found it!