Solved

How to use ADODB from VBA to retrieve / update SQL Server nvarchar, text, and ntext

Posted on 2004-04-07
1
3,716 Views
Last Modified: 2012-06-22
I'm enhancing a customer's application that has an Access 2002 front end mdb to a SQL Server back end. I use stored procedures to select and update data. Everything was working fine until I encountered some tables with large varchar, nvarchar, text, and ntext columns. In those cases I can't figure out what type to assign to my adodb parameters. For example, if I have as simple table with:

ID as indentity primary key
SVC as varchar(100)
LVC as varchar(4000)
SNVC as nvarchar(100)
LNVC as nvarchar(4000)
T1 as text
T2 as ntext

What does my VBA adodb command parameter look like for each of the above for a select stored procedure, i.e.

cmd.Parameters.Append .CreateParameter("@SVC", advarchar, adParamInput, 100)
cmd.Parameters.Append .CreateParameter("@LVC", ad?, adParamInput, ?)
cmd.Parameters.Append .CreateParameter("@SNVC", ad?, adParamInput, ?)
cmd.Parameters.Append .CreateParameter("@LNVC", ad?, adParamInput, ?)
cmd.Parameters.Append .CreateParameter("@T1", ad?, adParamInput, ?)
cmd.Parameters.Append .CreateParameter("@T2", ad?, adParamInput, ?)

Thx
0
Comment
Question by:rmk
1 Comment
 
LVL 26

Accepted Solution

by:
Alan Warren earned 250 total points
ID: 10779541
Hi rmk



ADO DataType Enum
http://www.able-consulting.com/ADODataTypeEnum.htm

' you can pass the value too as the last param, done the first one for you
dim sSVC as String
sSVC = "SomeString"
' or
' sSVC = me.sometextbox

cmd.Parameters.Append .CreateParameter("@SVC", adVarChar, adParamInput, 100, sSVC)
cmd.Parameters.Append .CreateParameter("@LVC", adVarChar, adParamInput, 4000)
cmd.Parameters.Append .CreateParameter("@SNVC", adVarWChar, adParamInput, 100)
cmd.Parameters.Append .CreateParameter("@LNVC", adVarWChar, adParamInput, 4000)
cmd.Parameters.Append .CreateParameter("@T1", adLongVarChar, adParamInput, 2147483647)
cmd.Parameters.Append .CreateParameter("@T2", adLongVarChar, adParamInput, 2147483647)


Alan
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question