IMPORT DATA TO ACCESS FROM REMOTE SQL SERVER HELP

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Simon Gare - 17 Mar 2007 20:00 GMT

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

Signature

Simon Gare
The Gare Group Limited

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

Reply to this Message

Albert D. Kallal - 17 Mar 2007 21:28 GMT

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).

Signature

Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@msn.com

Reply to this Message

Simon Gare - 17 Mar 2007 22:52 GMT

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

> The first approach is to get a linked table working.
>
[quoted text clipped - 14 lines]
> 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).

Reply to this Message

Albert D. Kallal - 17 Mar 2007 23:10 GMT

> Thanks Albert,
>
[quoted text clipped - 3 lines]
> 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...

Signature

Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@msn.com

Reply to this Message

Simon Gare - 17 Mar 2007 23:18 GMT

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

Thanks again Albert.

Regards
Simon

> > Thanks Albert,
> >
[quoted text clipped - 41 lines]
>
> Test the script...and test again...

Reply to this Message

Simon Gare - 18 Mar 2007 00:19 GMT

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

> > Thanks Albert,
> >
[quoted text clipped - 41 lines]
>
> Test the script...and test again...

Reply to this Message

Albert D. Kallal - 18 Mar 2007 07:57 GMT

"Simon Gare" <simon@simongare.com> wrote in message

> 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.

Signature

Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@msn.com

Reply to this Message

Simon Gare - 18 Mar 2007 15:26 GMT

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" <simon@simongare.com> wrote in message
>
[quoted text clipped - 40 lines]
> you certainly don't want that to occur in a scheduled program as there will
> be no user to answer the prompt.

Reply to this Message

Simon Gare - 18 Mar 2007 15:41 GMT

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

Thanks for all your help.

Regards
Simon

Reply to this Message

Simon Gare - 18 Mar 2007 21:37 GMT

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

> All solved Albert, weird one though just created a new db and worked fine.
>
[quoted text clipped - 48 lines]
> will
> > be no user to answer the prompt.

Reply to this Message

John Nurick - 18 Mar 2007 22:39 GMT

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.

>One last thing Albert,
>
[quoted text clipped - 62 lines]
>> will
>> > be no user to answer the prompt.

--
John Nurick [Microsoft Access MVP]

Please respond in the newsgroup and not by email.

Reply to this Message

Simon Gare - 18 Mar 2007 23:22 GMT

Thanks John,

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

Regards
Simon

> PMFJI,
>
[quoted text clipped - 74 lines]
>
> Please respond in the newsgroup and not by email.

Reply to this Message

Douglas J. Steele - 19 Mar 2007 00:24 GMT

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

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

> Thanks John,
>
[quoted text clipped - 85 lines]
>>
>> Please respond in the newsgroup and not by email.

Reply to this Message

Albert D. Kallal - 19 Mar 2007 00:19 GMT

> One last thing Albert,
>
[quoted text clipped - 3 lines]
>
> 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....

Signature

Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@msn.com

Reply to this Message