IMPORT DATA TO ACCESS FROM REMOTE SQL SERVER HELP

[Simon Gare]

Hi all,

I have a local Access DB which I need to import data from a remote SQL
server once a day, is there any way of doing this automatically?

regards
Simon

--
Simon Gare
The Gare Group Limited

website: www.thegaregroup.co.uk
website: www.privatehiresolutions.co.uk

 

[Albert D. Kallal]

The first approach is to get a linked table working.

Once you have that linked table working, then you can do a standard append
query, or retrieve data from that remote machine.

However, often, the admins of the remote sql database will NOT open it up to
a connection over the internet (too dangerous...lack of security if the data
is important).

Perhaps it might be better to have the folks on the remote sql machine setup
a script, or some "export" process that exports to a text file (csv). You
then have ms-access download that simple text file. This would eliminate
many security issues, and make your task quite simple (all you have to do is
download that text file..and import it).

Which of the above roads you take is going to much depend on the resources,
developers and how the remote machine can be opened up (you don't want
anyone to be able to open up and retrieve data from that remote system).

 

[Simon Gare]

Thanks Albert,

have the link working ok how do you automate the append query? Needs to
append data hourly or daily, can you give me some more info?

Regards
Simon

 

[Albert D. Kallal]

Thanks Albert,

have the link working ok how do you automate the append query? Needs to
append data hourly or daily, can you give me some more info?

Regards
Simon

Ah, excellent...you seem quite far down the curve...

Ok, you can use the windows scheduler to launch ms-access....


build your "append query", or whatever you do now. test it manually, and get
it working. Put the code in a standard sub.

Then, write a windows script that you will have the windows scheduler run
every day, hour...whatever.

To build a script, simply open up a notepad (.txt) file

Paste in the following:

dim accessApp
set accessApp = createObject("Access.Application")
accessApp.OpenCurrentDataBase("C:\Documents and Settings\Albert\My
Documents\Access\ScriptExample\MultiSelect.mdb")

accessApp.Run "TimeUpDate"
accessApp.Quit
set accessApp = nothing

msgbox "Job complete", 64

save the above, and then re-name the above .text file as a .vbs file. You
note the icon change. If you double click on the above, it will launch
ms-access, run a public sub in a standard code module called TimeUpDate.

Note you don't want any start-up forms etc running that could mess this up.
So, you might make a special "copy" of the mdb file with just your code and
the linked tables.


And, of course, once you get the script working, then remove the msgbox in
the above sample...as you can't schedule something with the msgbox prompt...

Test the script...and test again...

 

[Simon Gare]

Wow ! now that's what I call help, will let you know how I get on.

Thanks again Albert.

Regards
Simon

 

[Simon Gare]

Hi Albert,

just stuck on the

"Put the code in a standard sub"

bit, everything else is done click on .vbs file and error reads

Microsoft access can't find the procedure 'TimeUpDate'

even though I have created a module in access as follows, but this is where
the problem is,


Public Function TimeUpDate()

INSERT INTO booking
SELECT dbo_booking_form.*
FROM dbo_booking_form;

End Function

Just this last bit and I'm there.

Simon

 

[Albert D. Kallal]

bit, everything else is done click on .vbs file and error reads

Microsoft access can't find the procedure 'TimeUpDate'

You have to make it a sub, not a function...

Public Sub TimeUpDate()

Of cosue, you can use any name you want!!

INSERT INTO booking
SELECT dbo_booking_form.*
FROM dbo_booking_form;

Ok, you have to run the sql in that sub, so, you can either build query,
and go:

Public Sub TimeUpDate()

currentdb.QueryDefs("name of your query").Execute

end if

Or, could use your example of in-line sql, eg:

Public Sub TimeUpDate()

currentdb.Execute "INSERT INTO booking SELECT dbo_booking_form.* FROM
dbo_booking_form;"

end if

The approach here is to simply run some of your sql, or query in code. (use
whatever you been using in code to run sql or queries..that should work).

You can also use:

docmd.RunSQL "you sql"

However, using runsql will "prompt" you to confirm to run the update..and
you certainly don't want that to occur in a scheduled program as there will
be no user to answer the prompt.

 

[Simon Gare]

Hi Albert,

have followed your instructions to the letter and Public Sub is working and
inserting the data ok, but when I trigger the .vbs file I am still getting
error

Error: 'Microsoft Access can't find the procedure 'TimeUpDate.'
Code: 800A09D5
Source: (null)

Any ideas?

Regards
Simon

TimeUpDate.vbs file contents


dim accessApp
set accessApp = createObject("Access.Application")
accessApp.OpenCurrentDataBase("C:\CTI\amacexpressservices.mdb")

accessApp.Run "TimeUpDate"
accessApp.Quit
set accessApp = nothing

 

[Simon Gare]

All solved Albert, weird one though just created a new db and worked fine.

Thanks for all your help.

Regards
Simon

 

[Simon Gare]

One last thing Albert,

if I am putting this on a machine without Access installed I'm getting an
error
"ActiveX component cant create the object access.application"

Is there a workaround for this?

Simon

 

[John Nurick]

PMFJI,

If it's just a matter of running a query in an Access database, the
script DAO_Execute.vbs at
http://www.j.nurick.dial.pipex.com/Code/index.htm will probably do the
job.

 

[Simon Gare]

Thanks John,

actually its a Module called TimeUpDate would that make a difference?

Regards
Simon

 

[Albert D. Kallal]

One last thing Albert,

if I am putting this on a machine without Access installed I'm getting an
error
"ActiveX component cant create the object access.application"

Is there a workaround for this?

Well, that code is saying:

please launch ms-access.
please run this code in a module in ms-access
please shut down ms-access.

I would expect you realize that a script that tells the computer to load
ms-access, and then run code inside of ms-access is going to requite
ms-access on it!

It is true that every windows xp box ships with the jet data engine, and you
certainly don't need ms-access to read, and write data to a mdb file. (it is
the JET engine that does this...not ms-access). So, you can by-pass using
ms-access. You never did need ms-access on a compute to read data from a mdb
file.

However, to run code in a ms-access module...you are going to need ms-access
installed.

However, you can well eliminate the vb code, and simply execute/run your sql
directly in your vbs script....

Set dbeng = CreateObject("DAO.DBEngine.36")
strMdbFile = "C:\Documents and Settings\Albert\My
Documents\Access\ScriptExample\MultiSelect.mdb"
Set db = dbeng.OpenDatabase(strMdbFile)
strQuery = "select * from contacts"
Set rs = db.OpenRecordset(strQuery)

So, you can well dump the use of ms-access, and don't use a code module. If
you don't load ms-access, then you don't need it!!!

I not sure if you can use linked tables directly in a script however. (I
think you need ms-access for that -- you just have to test this)

Thus, you might need somting like (in addtion to the above script):

Set wrk1 = DBEngine.CreateWorkspace("TestWorkSpace", "", "", 1)

strCon = "ODBC;driver={SQL Server};DSN=;" _
& "SERVER=192.168.1.101;" _
& "DATABASE=RidesSql;" _
& "UID=SA;PWD="

Set MyCon = wrk1.OpenConnection("mycon", dbDriverNoPrompt, False, strCon)

' now, you have a regular connection, and can build a recordset as
' normal...

Set rstRecords = MyCon.OpenRecordset("select * from tblJunk")

The above code is in VBA...you might have to tweak it a bit (but, I would
try/test the linked table in dao, and see if that works....

 

[Douglas J. Steele]

If your sub (or function) is named TImeUpDate, you cannot name the module
TimeUpDate: module names have to be unique.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thanks John,

actually its a Module called TimeUpDate would that make a difference?

Regards
Simon