Convert from DAO to ADO

[BillyRogers]

I have some code in DAO that works fine on some of my coworkers computers but
not on others...it's not the references...I've checked. So I'm trying to
convert the code to ADO. What it does is it calls a stored procedure and
passess two parameters then it inserts the data into a table in another
database.

I'm not sure exactly how to convert this from DAO to ADO



--DAO version Works

strSQL = "Exec [GetCorpDemographics&Volume&CB] @MonthYearList='" &
MyCorpDates & "', @OrderList='" & MyCorpNums & "'"

Set qdfPassthrough = CurrentDb().QueryDefs("qryPassThru")
qdfPassthrough.Sql = strSQL


DoCmd.RunSQL "SELECT * INTO [CorpDemoVol] IN '" & AccessFilePath & "'
FROM [qryPassThru];"


--this is my attempt at an ADO version....I get an error on the last line

cmd.CommandText = "[GetCorpDemographics&Volume&CB]"
cmd.CommandType = adCmdStoredProc
cmd.CommandTimeout = 0
cmd.Parameters.Refresh
cmd.Parameters("@OrderList").Value = MyCorpNums
cmd.Parameters("@MonthYearList").Value = MyCorpDates




rst1.Open cmd

DoCmd.RunSQL "SELECT * INTO [CorpDemoVol] IN '" & AccessFilePath &
"' FROM rst1;" ' I get error message saying access doesn't recognize the
table rst1


--
Billy Rogers

Dallas,TX

Currently Using SQL Server 2000, Office 2000 and Office 2003

 

[Tony Toews [MVP]]

I have some code in DAO that works fine on some of my coworkers computers but
not on others...it's not the references...I've checked.

So tell us what the DAO problem is and we'll help you.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Billy Rogers]

I don't know what the DAO problem is ....that's why I want to try it with ADO.

It just doesn't work on some computers and works perfectly on others.

 

[George Nicholson]

I don't know what the DAO problem is ....

Well, what is the problem? What makes you say it is not working? Error
messages? Wrong results? Workstations explode?
(What makes you think its a DAO problem?)

You really need to nail down the "works on some, not on others" issue or
you are just flailing around grasping at straws.

Are all computers running the same version of Access? By version, I mean
2000, XP, 2003 etc. *AS WELL* as Service Pack #?

A simple trip to Microsoft Update might solve your problems.
It is highly unlikely that changing code to ADO will.

 

[Billy Rogers]

Hey George,

The reason i didn't go into the DAO problem is because that wasn't what my
question was about. I was asking about writing the code in ADO. I have a
previous post about the DAO problem.

Anyway I just found out a solution to the DAO problem. I needed to use a
DSN-Less connection like this.

ODBC;Driver={SQLServer};Server=myServer;DATABASE=mydatabase;Trusted_Connection=Yes

 

[Jamie Collins]

The reason i didn't go into the DAO problem is because that wasn't what my
question was about. I was asking about writing the code in ADO.

The replies you get in the Access newsgroups are more like those you'd
get from a politician at a press conference i.e. "Who has questions
for the answers I have? And please vote for me." ;-)

Jamie.

--

 

[Billy Rogers]

haha ....that's funny.

It would still be interesting to know how to fix that last line of ADO code
just for my own knowledge even though I don't need it anymore.

--
Billy Rogers

Dallas,TX

Currently Using SQL Server 2000, Office 2000 and Office 2003

http://thedataguru.blogspot.com/

 

[Robert Morley]

It's been a long time since I've done MDB stuff in ADO instead of ADP stuff,
but try changing the delimiters around the AccessFilePath to square brackets
and see if that works. Also remove the trailing semi-colon, as it's never
necessary AFAIK, and might be confusing ADO (don't think it should, but easy
enough to try).

DoCmd.RunSQL "SELECT * INTO [CorpDemoVol] IN [" & AccessFilePath & "] FROM
rst1"


Rob