473,372 Members | 947 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,372 software developers and data experts.

Upsized ACCESS Database to SQL Server 2000 with AS/400 Link

I have upsized an Access database into SQL Server and manged to get
the data in place ok. The wizard created an Access Project which I
have started to modify.

However, I am also trying to get data from an AS/400. Specifically for
a currency/exchange rate file. I only want ot copy the records since
the last copy otherwise there are a vast number of records.

I have created a linked server to the AS/400 within SQL Server, and
created a view, within my specific database which allows me to look at
all the data in the table.

However, my problem is that I want a VB routine, in the Access
Project, which lets me copy out specic records from the view into a
table within the SQL Server database. Any clues?????

My oroginal code used a link to the AS/400 and the folowing code:-

s = "INSERT into zacjdf00 "
s = s + "select CJCNCD, CJD5E9, CJCUCD, CJD5FF, CJD5FG, CJD5FH,
CJD5FI, CJD5FJ, CJD5FK "
s = s + "from XGHLDTM_ZACJDF00 "
s = s + "where "
s = s + "CJD5E9 = '" & group & "'"
DoCmd.RunSQL (s)

group is preset in the VB code.
Jul 20 '05 #1
1 3208

"Bob Davies" <da**********@virgin.net> wrote in message
news:9b************************@posting.google.com ...
I have upsized an Access database into SQL Server and manged to get
the data in place ok. The wizard created an Access Project which I
have started to modify.

However, I am also trying to get data from an AS/400. Specifically for
a currency/exchange rate file. I only want ot copy the records since
the last copy otherwise there are a vast number of records.

I have created a linked server to the AS/400 within SQL Server, and
created a view, within my specific database which allows me to look at
all the data in the table.

However, my problem is that I want a VB routine, in the Access
Project, which lets me copy out specic records from the view into a
table within the SQL Server database. Any clues?????

My oroginal code used a link to the AS/400 and the folowing code:-

s = "INSERT into zacjdf00 "
s = s + "select CJCNCD, CJD5E9, CJCUCD, CJD5FF, CJD5FG, CJD5FH,
CJD5FI, CJD5FJ, CJD5FK "
s = s + "from XGHLDTM_ZACJDF00 "
s = s + "where "
s = s + "CJD5E9 = '" & group & "'"
DoCmd.RunSQL (s)

group is preset in the VB code.


Assuming that your view works, and it is a view on the XGHLDTM_ZACJDF00
table, have you simply tried using your original query, but with updated
table names?

s = "INSERT into dbo.MyTable "
s = s + "select CJCNCD, CJD5E9, CJCUCD, CJD5FF, CJD5FG, CJD5FH,
CJD5FI, CJD5FJ, CJD5FK "
s = s + "from dbo.MyView "
s = s + "where "
s = s + "CJD5E9 = '" & group & "'"
DoCmd.RunSQL (s)

Alternatively, you could use pure SQL, perhaps in a stored procedure (which
would let you pass in the value as a parameter):

insert into dbo.MyTable
select CJCNCD, CJD5E9, CJCUCD, CJD5FF, CJD5FG, CJD5FH, CJD5FI, CJD5FJ,
CJD5FK
from dbo.MyView
where CJD5E9 = 'SomeValue'

If these suggestions don't help, perhaps you could clarify what you've tried
already, and any issues/errors that you have.

Simon
Jul 20 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: ErickR | last post by:
We are experiencing a problem with Sql Server 2000 linking to an Access 97 file. We have two machines that link to this .mdb file, and we recently upgraded one to newer hardware, SP3a, MDAC 2.8,...
3
by: *no spam* | last post by:
I want to move my Access 2K database into MSDE. The Access Upsizing Wizard crashes (a known bug wi A2K), so I'm using the following suggested method: Access --> New --> Project (Existing...
5
by: dananrg | last post by:
I've created a small company database where the tables reside in a SQL Server database. I'm using Access 2000 forms for a front end. I've got a System DSN set-up to SQL Server and am using links...
5
by: premmehrotra | last post by:
I currently have a multi-user access database which is put on a shared drive L: on a Windows Servers. Entire database is one file premdb.mdb. Users access this database from their laptops....
1
by: rcmail14872 | last post by:
I used the upsize wizard to change my Access 2002 database to a SQL Server ADP project. I had a form where the user entered a value into a text box and when a command button on the form was...
12
by: Mike | last post by:
I have an Access DB that I upsized to a SQL server DB. The tables that I upsized I can't seem to modify. I wanted to insert some data into the table and I am getting the following error: ...
6
by: baramee | last post by:
I develop web application by asp.net with ms access. In general , it work fine. But if hit rate of web is very much, it occure error 'Unspecified error'. it error on conn.open. My code will be...
1
by: Bruce Le Favre | last post by:
I upsized (using Upsize Wizard) ACCESS back end to MSSQL 2000. Front end is still ACCESS. I'm getting error when trying to add or updated a record entry from front end. The error message reads:...
1
by: marcf | last post by:
Ello everyone, Ok i've just upsized the database to SQL Server 2005, here is the code: Set con = Application.CurrentProject.Connection Set rs = New ADODB.Recordset rs.CursorType =...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.