Pass-Through Question

[Guest]

Hello, I am having a tough time relearning Access Anyway, I would
appreciate any help that I could get with the following problem. I have read
through the pass-through posts on this forum and confirmed what I suspected
in that a pass-through query cannot reference tables in the .mdb file.
However, that is precisely what I need to do. I am taking data from the .mdb
file and writing pass-through queries to upload the data into tables in an
MSDE database. I tried writing a non pass-through query to do this and
encountered all sorts of data conversion problems because the source Access
table has columns defined as text and the target MSDE table has datatypes of
datetime, nvarchar, etc.

Being a T-SQL programmer and not an MS Access programmer for some time, I
thought I would write a pass-through query using the CAST function to resolve
this issue. Well, that is when I figured out that a pass-through query
cannot reference an MS Access table in the .mdb file. I tried looking in the
MS Access on-line help and can't figure out if there is an MS Access
equivalent of CAST?

Anyway, I appreciate any advice anyone can provide.

Thanks in advance!

Tim S.

 

[Rick Brandt]

Hello, I am having a tough time relearning Access Anyway, I would
appreciate any help that I could get with the following problem. I
have read through the pass-through posts on this forum and confirmed
what I suspected in that a pass-through query cannot reference tables
in the .mdb file. However, that is precisely what I need to do. I am
taking data from the .mdb file and writing pass-through queries to
upload the data into tables in an MSDE database. I tried writing a
non pass-through query to do this and encountered all sorts of data
conversion problems because the source Access table has columns
defined as text and the target MSDE table has datatypes of datetime,
nvarchar, etc.

Being a T-SQL programmer and not an MS Access programmer for some
time, I thought I would write a pass-through query using the CAST
function to resolve this issue. Well, that is when I figured out
that a pass-through query cannot reference an MS Access table in the
.mdb file. I tried looking in the MS Access on-line help and can't
figure out if there is an MS Access equivalent of CAST?

Anyway, I appreciate any advice anyone can provide.

Thanks in advance!

Tim S.

Create a link to the MSDE table and then append to the link.

 

[John Spencer]

Specific functions that may work, but will have trouble with nulls
CStr convert to string
CLng conver to Long number
CDate, CSng, CDbl

Functions that should handle nulls
Val - Converts string to number (double), but it will convert "12abc" to 12. It
grabs all the leading numbers

DateValue (Truncates any time value in the string)
TimeValue (Truncates any date value in the string)


Functions to test for a valid value
IsDate
IsNumeric

There are probably others to help, but I can get to an Access Database or VBA
right now.

 

[Guest]

Hi Rick, thanks for the suggestion, but this didn't work. I got an error
message stating "operation not allowed on object type" or something like
that. What did work was to use a docmd.CopyDatabase and copy the table from
the local MS Access .mdb file into the instance of the MSDE I was using.
Then, I just used T-SQL to massage the data and perform the insert as I am
much more familiar with T-SQL.

Take care,

Tim