Collins, Darryl
Darryl.Collins at anz.com
Sun Oct 18 17:45:12 CDT 2009
Darren,
Depending of your permissons you could create the View in SQL Server
from Access using code, grab what you want from the view and then drop
the view in SQL Server. Not sure if that approach is acceptable for you
or not.
Cheers
Darryl
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Darren -
Active Billing
Sent: Friday, 16 October 2009 6:28 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Insert Into 101 For MAX
Howdy
Thanks - that's what I thought
I am not really at liberty to create stuff in the SQLSVR dB's - they
aren't mine
- I was hoping to be able to do it all within access
Many thanks
Darren
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Stuart
McLachlan
Sent: Friday, 16 October 2009 6:02 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Insert Into 101 For MAX
You need to create a View in SQL Server based on the query. You can
then link
to the
view just like you do with a table.
--
Stuart
On 16 Oct 2009 at 17:31, Darren - Active Billing wrote:
> Hi Stuart
>
> I have started to use this method for table to table 'links/import
>
> How would I go about doing this where I would like to use a query on
> the SQL Server Side instead of a table on the SQL Server side
>
> EG this is what I'd like to do - but of course an SQL statement is not
> an
object
> that the transferSpreadsheet routine can find
>
> Dim selSQL as string
> selSQL = "Select *, aa.SomeOtherFieldFromSomeOtherTable as SomeField
> from Account join SomeOtherTable aa on a.AccountNo = aa.AccountNo"
>
> DoCmd.TransferDatabase acLink, "ODBC Database", strODBCConn, acTable,
> selSQL, "tblAccount", False
>
> Thanks
>
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Stuart
> McLachlan
> Sent: Tuesday, 13 October 2009 10:42 PM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Insert Into 101 For MAX
>
> You could use ODBC - link the SQL Server table, do the insert and then
> delete the link.
>
> This should do it (aircode so may need some tweaking):
>
> Local strODBCConn as string
> Local strSQL as string
>
> strODBCConn = "ODBC;Description=Test Connection;DRIVER=SQL Server;" &
_
> "SERVER=SQLServername;APP=Microsoft Data Access Components;"
& _
> "DATABASE=SQLDatabasename;Trusted_Connection=Yes"
>
> 'Create ODBC Link
> DoCmd.TransferDatabase acLink, "ODBC Database", strODBCConn, acTable,
_
> "tblX", "tblX", False
>
> 'Insert records
> strSQL = "Insert into tblLocalTable Select * from tblX"
> CurrentDb.Execute strSQL
>
> 'Delete ODBC Link
> DoCmd.DeleteObject acTable, "tblX"
>
> --
> Stuart
>
>
> On 13 Oct 2009 at 21:55, Darren - Active Billing wrote:
>
> > Hi Max
> >
> > This looks promising - How would you get it to connect to SQL server
> > on
table
> X
> > and then populate a local Access Table with all the records from
table X?
> > Many thanks
> > Darren
> > -----Original Message-----
> > From: accessd-bounces at databaseadvisors.com
> > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Max
> > Wanadoo
> > Sent: Tuesday, 13 October 2009 3:40 AM
> > To: 'Access Developers discussion and problem solving'
> > Subject: Re: [AccessD] Insert Into 101
> >
> > As far as I understand the problem, this works ok for me.
> >
> > Max
> >
> > Sub AppendRecsFromQuery()
> > Dim dbs As DAO.Database, rst As DAO.Recordset
> > Dim sql As String, qdf As DAO.QueryDef
> > Set dbs = CurrentDb
> > sql = "Drop table _TestA"
> > dbs.Execute (sql)
> >
> > sql = "Create Table _TestA (FirstName text, Surname Text, PersonID
long)"
> > dbs.Execute (sql)
> >
> > sql = "Drop Table _TestQ"
> > dbs.Execute sql
> >
> > sql = "Select firstname,surname,PersonID from mcmPeople Where
> > PersonID < 1000"
> > Set qdf = dbs.CreateQueryDef("_TestQ", sql)
> > Set rst = dbs.OpenRecordset(qdf.Name, dbOpenSnapshot)
> > sql = "insert into _TestA " & qdf.sql
> > dbs.Execute sql
> >
> > End Sub
> >
> >
> >
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> >
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
"This e-mail and any attachments to it (the "Communication") is, unless otherwise stated, confidential, may contain copyright material and is for the use only of the intended recipient. If you receive the Communication in error, please notify the sender immediately by return e-mail, delete the Communication and the return e-mail, and do not read, copy, retransmit or otherwise deal with it. Any views expressed in the Communication are those of the individual sender only, unless expressly stated to be those of Australia and New Zealand Banking Group Limited ABN 11 005 357 522, or any of its related entities including ANZ National Bank Limited (together "ANZ"). ANZ does not accept liability in connection with the integrity of or errors in the Communication, computer virus, data corruption, interference or delay arising from or in respect of the Communication."