Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Updating SQL through Access

Status
Not open for further replies.

deharris2003

Programmer
Jul 1, 2003
41
US
Hi, I have created a small function that should update an SQL table based on a local table stored in Access. Problem is I cannot get the function to recoginize the local table as a valid table. Any help is apreciated here is what i currently have

Public Function UpdateAetnaSource()
'------------------------------------------------------------------
'This function is used to automate the process adding new records
'to the Aetna_Leakeage table for the end user.
'This was created as an Alternative to giving the end user
'direct access to the SQL Database table
'------------------------------------------------------------------
Dim Conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim db As Database
Dim Table As TableDef

Set Conn = CreateObject("ADODB.Connection")

Conn.Open "DSN=TSU;UID=Test;PWD=temp"

strSQL = "INSERT INTO [DBO].Aetna_Leakage " & _
"SELECT Temp_Data.* " & _
"FROM Temp_Data"

Conn.Execute strSQL

Conn.Close
Set Conn = Noth
 
What error are you getting? Are you able to do inserts to the target table using a query?
 
I get an invalid object name error. I can run this query fine using a pas-through query in Access.
 
Try these changes:

Set Conn = New ADODB.Connection

Conn.Open "DSN=TSU;UID=Test;PWD=temp"

If conn.state=adStateOpen then

strSQL = "INSERT INTO [DBO].Aetna_Leakage " & _
"SELECT Temp_Data.* " & _
"FROM Temp_Data"

Conn.Execute strSQL

else
msgbox "You've got a bad DB connection"


End if


 
Ok,so the connection is actually open. It doesn't like your SQL string for some reason.

INSERT INTO [DBO].Aetna_Leakage

Is this a MS SQL server table ? If so it probably doesn't like the "[DBO]." Is Aetna_leakage the name of the table or is it a data field in the table?
 
I have to split. If Aetna_Leakage is the name of the table, try replacing [DBO].Aetna_Leakage with either DBO_Aetna_Leakage.* or Aetna_Leakage.* and see if that works.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top