Type mismatch in linked tables. Please help!

[Guest]

Hi, I'm a new user and I'm trying to run a query from some linked tables,
however I am getting a message saying type mismatch. I understand that fields
which 'match' (those which I've created a relationship between) in each table
must be the same type, however I cannot change the type in design view as it
says 'MS Access can't save property changes for linked tables'. So, I tried
to change the format of cells (all to text) directly in the excel file and
linked the table again, but this did not work. Some fields are text and some
are numbers. Does anyone have a solution to this problem? Any help would be
greatly appreciated! (I'm using MS Access 2000). Thanks!

 

[Guest]

Change property in the source table to which the link is pointing, i.e., in
the table in another database which was used to create the linked table.
--
********* http://panjas.org
If the message was helpful to you, click Yes next to Was this post helpful
to you?
If the post answers your question, click Yes next to Did this post answer
the question?

 

[Guest]

Hi, sorry I'm not sure if I follow. I created the table directly from excel
(in access I clicked new, chose 'link table', found my excel file and
followed the prompts). In this case, am I correct to say that the 'source
table to which the link is pointing' is the excel file? I have tried to
change the excel file and this did not work. Unless I am not changing it
correctly. I highlighted the data, selected 'Format' and chose 'text'. Is
there another way?

Thanks.

 

[Guest]

Hi BRN,

Changing a format in Excel will not do the trick, as you have discovered.
You need to actually set each cell in the affected column to text. One method
would be to edit the contents of each cell, by adding a single quote at the
beginning. Of course, that can get very painful real quick. Another method is
to create a macro to do the heavy lifting for you. Here is an example of a
macro that I use for this purpose:


Sub NumbersToText()
' Select the column of data to convert to text
' Keyboard Shortcut: Ctrl+Shift+T
'
' Note: TextToColumns only works on one column at a time, so
' you'd have to build a For...Next loop to do multiple columns.

With Selection
If .Cells.Count > 1 And ActiveCell.Value <> "" Then _
.TextToColumns _
FieldInfo:=Array(1, xlTextFormat), _
DataType:=xlDelimited
End With


End Sub


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Aaron Kempf]

if you think that linked tables are too muhc of a pain in the butt, then you
should use Access Data Projects