ODBC to Access Dumping Question

[Chris]

Hi Community,

First, I wasn't sure how to describe the question, so a sad Subject line was
created.
Second, I come from a LAN/Hardware side of Information Technology but am
dabling with some basic VB.NET and Access DB programming to accomplish a few
things for our Retail side.

We are currently using an application that uses a C-ISAM db. We can pay for
Transoft ODBC licenses to connect to this db - which we have a few.

What I have done for a project is imported 3 tables into an Acces DB and
have designed a small VB app around it. This works great. The challenge is I
need to import the tables frequently and distribute to another network
resource. This takes time.

What I want to do is:

* Create a program/script on our server that uses the Transoft ODBC to
connect to the DSN, import the 3 tables I need to an Access DB.
* Copy the Access DB to a path on the network or to a specific machine
on a daily basis.

Is this possible to script or write something in VB.Net?

 

[Alex Dybenko]

Hi,
you can certainly do this in Access VBA
And I think on VB.Net also

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[Chris]

Alex,

Any chance you can show an example of this (in VBA) ?

Regards,
Chris

 

[Alex Dybenko]

Hi,
to import - you can use docmd.transferdatase
OR create linked tables to your odbc dsn and then run append queries to copy
date

to copy file - you can use FileCopy statement

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[David W. Fenton]

Is this possible to script or write something in VB.Net?

It's not clear to me what you're using the data for, but given that
you're importing it regularly and distributing it, I assume it's
read-only, used only for reporting/analysis purposes.

From what you've said it's not clear if you're using Access at all.
You appear to be importing the data into an MDB file, but that's not
Access -- that's Jet.

If you did your reporting in Access, it would make sense to use
linked ODBC tables as the basis for your reports.

If you're creating your front end in VB.NET, then Access is not
involved and it's not clear to me why you just wouldn't use ODBC
connect strings to directly use the data in your non-Access
application.

Now, it is true that reporting with its extensive sorting and
grouping can sometimes impose some significant read locks. Because
of that it might make sense to import the data and use that. If
you're using Access, this is really simple. If you're not actually
using Access, it's not so simple and you're likely to not get much
assistance from a bunch of Access programmers, precisely because
it's no longer an Access question at all.

 

[david]

here is an example of using ODBC in VBscript:

dim dbe
dim db
dim sql
dim filesys

sql = "select table_a.* into table_b from [odbc;myodbcstring].[table_a]"
set dbe = createobject("dao.dbengine.4")
set db = dbe.createdatabase("myname",";LANGID=0x0409;CP=1252;COUNTRY=0")
db.execute(sql)

set filesys=CreateObject("Scripting.FileSystemObject")
If filesys.FileExists("myname") Then
filesys.CopyFile ""myname, "c:\destfolder\"
End If

air code: i haven't checked it.

(david)

 

[Chris]

Daivd,

You are correct - I will not be using Access directly - but the JET
connection to an MDB. My ignorance showing through.

The purpose of this exercise is really a fix for a much larger issue we have
- that the company doesn't want to spend money to fix.

Our retail side (Farm Supplies, Chemicals etc) is located 4 buildings over
from our main office. Our server is located in the main office. Our entire
network is really strands of cat5e with several switches. The cat5e is
burried underground as it goes from building to building. I've been here a
year and my best efforts to do something about it have failed - but that's
another story.

The issue is if a switch or network connection fails, our entire retail side
can no longer use the ERP software (with POS features) to get prices, check
inventory and effectively make a sale. The idea here is to "import" using
ODBC the 3 tables that I require and design a simple PriceCheck application
using VB. Once the mdb file is created - schedule a copy of that mdb to the
4 desktops that need it.

This is not hard to do, it can be done manually. I just wish to automate the
importing process.

I have posted the question in the appropriate programming community, and
appreciate the time spent for those that answered.

Cheers,
Chris

 

[David W. Fenton]

The issue is if a switch or network connection fails, our entire
retail side can no longer use the ERP software (with POS features)
to get prices, check inventory and effectively make a sale. The
idea here is to "import" using ODBC the 3 tables that I require
and design a simple PriceCheck application using VB. Once the mdb
file is created - schedule a copy of that mdb to the 4 desktops
that need it.

OK, so the linked table solution won't work, since you're trying to
work around severe latency problems (to put it very politely!).

I can't advise you on the code to do this from VB.NET. It can be
done in plain old Jet SQL which ought to be executable via OLEDB
without problems. You'll probably want to test the SQL in Access to
see that it works, but once it works, shouldn't be an issue.

I think that I'd likely set up my app to check for the available
connection, and either import the current data if the connection is
available, or use the data directly. Or do both, so that the most
recent data will be available later.

Another option would be to schedule a task to do this on a regular
basis. It's perfectly possible to write a VBScript that does all of
this, in fact, but I don't have the expertise to advise you on this.

In short, Jet is only your data store, and thus doesn't really have
much to do with the programming problem (and Access has nothing to
do with the programming problem). As long as you get your connect
strings right and use Jet's SQL dialect, you'll be fine.