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

[Bob Davies]

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.

[Simon Hayes]

"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