Converting VBA code into VBScript

[HamotIntern]

I have a piece of code that I wrote in VBA that I need to convert to VBScript.


DoCmd.TransferDatabase acImport, "ODBC","ODBC;DSN=CareVue;UID=xxxxx;PW­D=xxxx", acTable, "tablename", "tablename"


Could someone please help me?

 

[Craig0201]

You'll need to instantiate an Access application object.

Dim app

Set app = CreateObject("Access.Application")
app.DoCmd.TransferDatabase acImport, "ODBC","ODBC;DSN=CareVue;UID=xxxxx;PW­D=xxxx", acTable, "tablename", "tablename"

Craig

 

[HamotIntern]

I do appreciate the suggestion, but we’re going to be running this code on a server and would prefer not to have Access running. The script will be ran within SQL Server 2000. I guess I should have included that in my initial post.

 

[PHV]

I you don't want Access running then don't use any access's object.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Craig0201]

How are you going to use DoCmd if there is no Access app???

Try DTS if you don't want to use DoCmd.

 

[HamotIntern]

I think it is time I give a full background to the question. Perhaps it will help you to give me the answer I seek. We have a program called CareVue. It has an AllBase database. We have an ODBC driver for it. What we want to do is have the SQL Server import the data/tables from AllBase into it on a regular basis. Since I was more familiar with Access than I am with SQL Server, I wrote a “model” VBA script in it (Actually I created a macro and then modified the code from it). This code worked great. Now, I need to write the script (either jscript for VBScript) for the SQL Server so we can incorporate it into a procedure. I hope I am making sense, as my screen name indicates I am an intern and am learning as I go.

 

[Craig0201]

I think you're getting tied up between the differences of Functional (what you need to do) and Technical (how you do it) Specs. What you have done is used Access to template a Functional Spec but now can't see that there are multiple Technical Specs to meet a Functional Spec. Your functional spec is to transfer data. Your prototype is in VBA but there is no necessity for it to remain that way.

If you want to use VBScript and DoCmd you MUST reference Access as I have. If you don't want to use Access, you'll need to research another solution to meet the Functional requirement. For SQL Server, this may well be the SQL Server DTS, in particular the Transform Data Task.