Fetching Data From Another Database And Running Insert Query Advice 1

[DomDom3]

Hi there,

I'm trying to fetch data from a table in another database and insert it into a table in the current database.

Dim strConnection, conn, rs, strSQL
strConnection = "DRIVER={SQL server};SERVER=zfrdc003;DATABASE=SitetrackerTest;ID=Integrationdb;pwd=I&A;"
Set conn = CreateObject("ADODB.Connection")
conn.Open strConnection
Set rs = CreateObject("ADODB.recordset")
DoCmd.RunSQL "INSERT INTO [WorkPackages] ([Planned Start Date]) SELECT [Date] FROM vReport_WorkPackage_Dates WHERE WPID = " & "2878"

Using the above code I get an invalid object error on vReport_WorkPackage_Dates. I presume there is some confusion as to which table is in which database.

Can anyone advise?

 

[AlexCuse]

from rs.vReport_WorkPackage_Dates

will do the trikc I think.

Hope it helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[DomDom3]

Thanks for your reply, I still get a run time 208 error -invalid object on the

rs.vReport_WorkPackage_Dates

this time.

 

[AlexCuse]

On second look, you are missing the opening of your recordset.

something like this:

rs.open "select [Date], WPID from vReport_WorkPackage_Dates"

You then would refer to it in your update as simply rs.[Date]

Hoope this helps,

Alex



Ignorance of certain subjects is a great part of wisdom

 

[Golom]

Try something like

Dim SQL As string

SQL = "INSERT INTO [WorkPackages] ([Planned Start Date]) " & _
      "SELECT [Date] " & _
      "FROM [red][;Database=""C:\SomePath\Somedb.mdb"").vReport_WorkPackage_Dates[/red] " & _
      "WHERE WPID = '2878'

 

[PHV]

And what about something like this ?
strConnection = "DRIVER={SQL server};SERVER=zfrdc003;DATABASE=SitetrackerTest;ID=Integrationdb;pwd=I&A;"
Set conn = CreateObject("ADODB.Connection")
conn.Open strConnection
Set rs = CreateObject("ADODB.Recordset")
rs.open "SELECT [Date] FROM vReport_WorkPackage_Dates WHERE WPID=2878"
If Not (rs.BOF Or rs.EOF) Then
DoCmd.RunSQL "INSERT INTO [WorkPackages] ([Planned Start Date]) VALUES (#" & Format(rs.Fields(0), "yyyy-mm-dd") & "#)"
End If
rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[DomDom3]

Thanks your repsonses,

PH, I'm getting there I think...it's now picking up a value and entering it in the table although its not correct.

Its picking up 25/12/2006 00:00:00 (smalldatime format) (UK dates) but the value in the table when I look is 18/07/1894 (datetime format).

I imagine there's no formatting problems between the 2 - any ideas?

 

[DomDom3]

It seems rather than picking up the wqhole date it's only picking up the first 2 digits - 25, hence the incorrect date.

Can anyone advise on how I can pick up the full date dd-mm-yyyy?

Thanks

 

[DomDom3]

Moved to thread705-1311407