Re: Two questions: Access and SQL Server/VBA

"David" <consulttech2004@xxxxxxxxxxx> wrote in message
news:467ecd70-b42b-4767-8dd6-875290d40432@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
I am writing VBA code using Access 2003 as a front end, and SQL Server
tables as the back end.

I have code that loops through the field names on a query and returns
the field names to another table. Easy enough. Now the hard parts:

1. How is the easiest way to get the data types from the SQL Server
Tables? I can't seem to return them. Is this because I am looking at
queries, or because where Access says "Text" SQL Server is really
saying "NVARCHAR" or something like that?


Are you using a access data project, or a odbc + linked tables?

I would ask in regards to the above is how did you do this before? Your code
should work as it does now if you using linked tables. Try bringing up a
table in design view, and you'll see that access when using linked tables
uses the previous data types. This means all your code and programming
should and can assume that those data types behave as before. So no real
particular change here.

The only possible wildcard here is if you're NOT using linked tables and
using an access data project. In the case of linked tables, you will find
that if you use a pass-through query, then often the correct data length and
data types DO NOT come through correctly into access from the server side.
In this case what I do is create a query based on a linked table to the back
end database. I use that query to append data with conditions that return
ZERO records (rows). That winds up creating your table locally with the
correct data types (and more importantly the correct data (text) lengths).
You can then use a pass-though query for the high speed data transfer
(appending).

Another possible work around is if you have a copy of the table created
locally and this allows you to control the data types (and text lengths)
somewhat better. If the tables are already locally created then you don't
have this problem of the data types incorrectly coming through from SQL
server when you use a pass through query.


2. In SQL Server, there is a place in the table design for field
descriptions. When I enter a field description for a field and relink
my table, my descriptions don't seem to come across to Access. Is
this a common problem?

I don't believe the table descriptions ever did come across. Since linked
tables might be an Oracle database, or MySql, or Sybase, or Pick/d3, or
insert "your favorite databae system here", then that database may or may
not even support table descriptions. I don't believe table descriptions have
been part of any particular ODBC standard for the last 20+ years of the
computer industry. So in the database history and looking at the history of
databases and computing, there's no reasonable reason to "assume" that this
information would come through correctly. I suppose it would be nice, but
the last twenty years of ODBC would hint this is not the norm...


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@xxxxxxx


.

Relevant Pages

  • Re: sp_addtype and sp_droptype
    ... I completely negleted the views -- and the Enterprise Manager neglected the ... > This query will give you the table names where the data types are binded. ... >> My database has three user defined types which reference varchar. ...
    (microsoft.public.sqlserver.programming)
  • Collection Capacity
    ... it is a "data types" question, but I hit it in an aspx ... I am recovering a large collection of query ... data from a database, and each row is used to ... Arithmetic operation resulted in an overflow ...
    (microsoft.public.dotnet.distributed_apps)
  • Re: sp_addtype and sp_droptype
    ... Execute the below query from your database. ... Replace the empno,empnum user defined datatype names with your actual names. ... This query will give you the table names where the data types are binded. ...
    (microsoft.public.sqlserver.programming)
  • Re: reserved error -1524 access 2007
    ... OT and cod are the same type ... If OT is a query, ... difficult to trace what's going on without details of the field types. ... It might be that JET is getting the data types mixed up. ...
    (microsoft.public.access.queries)
  • Re: Query Too Complex
    ... what data types are in use at each level? ... > the grouping levels, however this is by no means ideal. ... > Do you know of anything I can do to make this report run with the number ... >> how to run the query. ...
    (microsoft.public.access.reports)