[TA0523]

Sub GetDataFromExecl()

    'Dim dbsTemp As Database
    'Dim tdfLinked As TableDef
     
     CurrentDb.TableDefs.Delete ("Test")
     
     Set dbsTemp = CurrentDb
     Set tdfLinked = dbsTemp.CreateTableDef("Test")

     tdfLinked.Connect = _
     "Excel 5.0;HDR=YES;IMEX=2;DATABASE=V:\Common\GL\Extract Files\2008\MC_0908.xls"

     tdfLinked.SourceTableName = "AP_INPUT$"
     dbsTemp.TableDefs.Append tdfLinked
     
     DoCmd.RunCommand "SELECT * INTO ta1 FROM Test;"
     
     
     
     
End Sub

Mod Edit: Please use code tags when posting your code. Code tags are used like so =>

Thanks,
PsychoCoder

[thava]

when you need to insert data to a table you can follow these things according to your need

need1
when you want to update only once you need to concentrate on data's
when there is data lose you need to correct those things later for that you may set a separate field in the destination table or store the primary key value of the destination table in a separate field in the source table this would help if you find any thing wrong in your updation later

need2
when you update regularly to a fixed definition table you should follow same in the above need also need to check the time to take complete a updatiion

need3
when you update regularly to a changed definition table you should follow same in the above need also need to check the definition of the table because it could be changed

better you use an ADO connection instead of DAO Connection

[TA0523]

TA0523, on 23 Oct, 2008 - 01:49 PM, said:

Sub GetDataFromExecl()

    'Dim dbsTemp As Database
    'Dim tdfLinked As TableDef
     
     CurrentDb.TableDefs.Delete ("Test")
     
     Set dbsTemp = CurrentDb
     Set tdfLinked = dbsTemp.CreateTableDef("Test")

     tdfLinked.Connect = _
     "Excel 5.0;HDR=YES;IMEX=2;DATABASE=V:\Common\GL\Extract Files\2008\MC_0908.xls"

     tdfLinked.SourceTableName = "AP_INPUT$"
     dbsTemp.TableDefs.Append tdfLinked
     
     DoCmd.RunCommand "SELECT * INTO ta1 FROM Test;"
     
     
     
     
End Sub

Mod Edit: Please use code tags when posting your code. Code tags are used like so =>

Thanks,
PsychoCoder

Sub GetDataFromExecl()  
  
	'Dim dbsTemp As Database  
	'Dim tdfLinked As TableDef  
	   
	 CurrentDb.TableDefs.Delete ("Test")  
	   
	 Set dbsTemp = CurrentDb  
	 Set tdfLinked = dbsTemp.CreateTableDef("Test")  
  
	 tdfLinked.Connect = _  
	 "Excel 5.0;HDR=YES;IMEX=2;DATABASE=V:\Common\GL\Extract Files\2008\MC_0908.xls"  
  
	 tdfLinked.SourceTableName = "AP_INPUT$"  
	 dbsTemp.TableDefs.Append tdfLinked  
	   
	 DoCmd.RunCommand "SELECT * INTO ta1 FROM Test;"  
	   
	   
	   
	   
End Sub  

THis code does work, but when I run it it asked to over write the file / table. I want to be able to run it a number of times (each month) and add / append data to the same file...?

Thanks for your help...

[thava]

i already told that
ok here just change the query
like this

INSERT INTO Ta1 SELECT * FROM test

this is work for me

[TA0523]

TA0523, on 24 Oct, 2008 - 06:10 AM, said:

TA0523, on 23 Oct, 2008 - 01:49 PM, said:

Sub GetDataFromExecl()

    'Dim dbsTemp As Database
    'Dim tdfLinked As TableDef
     
     CurrentDb.TableDefs.Delete ("Test")
     
     Set dbsTemp = CurrentDb
     Set tdfLinked = dbsTemp.CreateTableDef("Test")

     tdfLinked.Connect = _
     "Excel 5.0;HDR=YES;IMEX=2;DATABASE=V:\Common\GL\Extract Files\2008\MC_0908.xls"

     tdfLinked.SourceTableName = "AP_INPUT$"
     dbsTemp.TableDefs.Append tdfLinked
     
     DoCmd.RunCommand "SELECT * INTO ta1 FROM Test;"
     
     
     
     
End Sub

Mod Edit: Please use code tags when posting your code. Code tags are used like so =>

Thanks,
PsychoCoder

Sub GetDataFromExecl()  
  
	'Dim dbsTemp As Database  
	'Dim tdfLinked As TableDef  
	   
	 CurrentDb.TableDefs.Delete ("Test")  
	   
	 Set dbsTemp = CurrentDb  
	 Set tdfLinked = dbsTemp.CreateTableDef("Test")  
  
	 tdfLinked.Connect = _  
	 "Excel 5.0;HDR=YES;IMEX=2;DATABASE=V:\Common\GL\Extract Files\2008\MC_0908.xls"  
  
	 tdfLinked.SourceTableName = "AP_INPUT$"  
	 dbsTemp.TableDefs.Append tdfLinked  
	   
	 DoCmd.RunCommand "SELECT * INTO ta1 FROM Test;"  
	   
	   
	   
	   
End Sub  

THis code does work, but when I run it it asked to over write the file / table. I want to be able to run it a number of times (each month) and add / append data to the same file...?

Thanks for your help...

Sub GetDataFromExecl()

	'Dim dbsTemp As Database
	'Dim tdfLinked As TableDef
	 
	 'CurrentDb.TableDefs.Delete ("Test")
	 
	 Set dbsTemp = CurrentDb
	 Set tdfLinked = dbsTemp.CreateTableDef("Test")

	 tdfLinked.Connect = _
	 "Excel 5.0;HDR=YES;IMEX=2;DATABASE=V:\Common\GL\Extract Files\2008\MC_0908.xls"

	 tdfLinked.SourceTableName = "AP_INPUT$"
	 dbsTemp.TableDefs.Append tdfLinked
	 
	 SQL = "INSERT INTO ta1 SELECT * FROM test;"
	 

I ran the query with your change "INSERT INTO ta1 SELECT * FROM test". It seems to run, but no updates are taking place? No adding of records?

Thanks

[thava]

just clarify some thing did you use a predefined table or
you create the table at the time of macro executing

[TA0523]

TA0523, on 24 Oct, 2008 - 12:23 PM, said:

TA0523, on 24 Oct, 2008 - 06:10 AM, said:

TA0523, on 23 Oct, 2008 - 01:49 PM, said:

Sub GetDataFromExecl()

    'Dim dbsTemp As Database
    'Dim tdfLinked As TableDef
     
     CurrentDb.TableDefs.Delete ("Test")
     
     Set dbsTemp = CurrentDb
     Set tdfLinked = dbsTemp.CreateTableDef("Test")

     tdfLinked.Connect = _
     "Excel 5.0;HDR=YES;IMEX=2;DATABASE=V:\Common\GL\Extract Files\2008\MC_0908.xls"

     tdfLinked.SourceTableName = "AP_INPUT$"
     dbsTemp.TableDefs.Append tdfLinked
     
     DoCmd.RunCommand "SELECT * INTO ta1 FROM Test;"
     
     
     
     
End Sub

Mod Edit: Please use code tags when posting your code. Code tags are used like so =>

Thanks,
PsychoCoder

Sub GetDataFromExecl()  
  
	'Dim dbsTemp As Database  
	'Dim tdfLinked As TableDef  
	   
	 CurrentDb.TableDefs.Delete ("Test")  
	   
	 Set dbsTemp = CurrentDb  
	 Set tdfLinked = dbsTemp.CreateTableDef("Test")  
  
	 tdfLinked.Connect = _  
	 "Excel 5.0;HDR=YES;IMEX=2;DATABASE=V:\Common\GL\Extract Files\2008\MC_0908.xls"  
  
	 tdfLinked.SourceTableName = "AP_INPUT$"  
	 dbsTemp.TableDefs.Append tdfLinked  
	   
	 DoCmd.RunCommand "SELECT * INTO ta1 FROM Test;"  
	   
	   
	   
	   
End Sub  

THis code does work, but when I run it it asked to over write the file / table. I want to be able to run it a number of times (each month) and add / append data to the same file...?

Thanks for your help...

Sub GetDataFromExecl()

	'Dim dbsTemp As Database
	'Dim tdfLinked As TableDef
	 
	 'CurrentDb.TableDefs.Delete ("Test")
	 
	 Set dbsTemp = CurrentDb
	 Set tdfLinked = dbsTemp.CreateTableDef("Test")

	 tdfLinked.Connect = _
	 "Excel 5.0;HDR=YES;IMEX=2;DATABASE=V:\Common\GL\Extract Files\2008\MC_0908.xls"

	 tdfLinked.SourceTableName = "AP_INPUT$"
	 dbsTemp.TableDefs.Append tdfLinked
	 
	 SQL = "INSERT INTO ta1 SELECT * FROM test;"
	 

I ran the query with your change "INSERT INTO ta1 SELECT * FROM test". It seems to run, but no updates are taking place? No adding of records?

Thanks

The table is linked via the excel file "Test"... so I think that I created the table at the time of macro executing (I think???) Does that help?

[thava]

i want to know whether ta1 is predefined or not if it's predefined it should be work or else try create a tabledef like you have previously done this should run only once in you r database and execute the insert into query

iam sure this code is work for me


hey what about your

Docmd.runcommand

did you forget this

This post has been edited by thava: 29 October 2008 - 04:56 AM

[TA0523]

thava, on 29 Oct, 2008 - 04:54 AM, said:

i want to know whether ta1 is predefined or not if it's predefined it should be work or else try create a tabledef like you have previously done this should run only once in you r database and execute the insert into query

iam sure this code is work for me


hey what about your

Docmd.runcommand

did you forget this

Quote

I got an error message


"Run Time Error 13"

"Type Mismatch"

any idea???

[thava]

where did you get this error

[TA0523]

thava, on 29 Oct, 2008 - 08:01 AM, said:

where did you get this error

Quote

Sub GetDataFromExecl()

'Dim dbsTemp As Database
'Dim tdfLinked As TableDef

CurrentDb.TableDefs.Delete ("Test")

Set dbsTemp = CurrentDb
Set tdfLinked = dbsTemp.CreateTableDef("Test")

tdfLinked.Connect = _
"Excel 5.0;HDR=YES;IMEX=2;DATABASE=V:\Common\GL\Extract Files\2008\MC_1008.xls"

tdfLinked.SourceTableName = "AP_INPUT$"
dbsTemp.TableDefs.Append tdfLinked


'SQL = "INSERT INTO ta1 SELECT * FROM Test;"

DoCmd.RunCommand "INSERT INTO ta1 SELECT * FROM Test;"

End Sub

I get it on the "DoCmd.RunCommand" above...

[TA0523]

TA0523, on 3 Nov, 2008 - 08:53 AM, said:

thava, on 29 Oct, 2008 - 08:01 AM, said:

where did you get this error

Quote

Sub GetDataFromExecl()

'Dim dbsTemp As Database
'Dim tdfLinked As TableDef

CurrentDb.TableDefs.Delete ("Test")

Set dbsTemp = CurrentDb
Set tdfLinked = dbsTemp.CreateTableDef("Test")

tdfLinked.Connect = _
"Excel 5.0;HDR=YES;IMEX=2;DATABASE=V:\Common\GL\Extract Files\2008\MC_1008.xls"

tdfLinked.SourceTableName = "AP_INPUT$"
dbsTemp.TableDefs.Append tdfLinked


'SQL = "INSERT INTO ta1 SELECT * FROM Test;"

DoCmd.RunCommand "INSERT INTO ta1 SELECT * FROM Test;"

End Sub

I get it on the "DoCmd.RunCommand" above...

[TA0523]

TA0523, on 3 Nov, 2008 - 08:41 AM, said:

TA0523, on 3 Nov, 2008 - 08:53 AM, said:

thava, on 29 Oct, 2008 - 08:01 AM, said:

where did you get this error

Quote

Sub GetDataFromExecl()

'Dim dbsTemp As Database
'Dim tdfLinked As TableDef

CurrentDb.TableDefs.Delete ("Test")

Set dbsTemp = CurrentDb
Set tdfLinked = dbsTemp.CreateTableDef("Test")

tdfLinked.Connect = _
"Excel 5.0;HDR=YES;IMEX=2;DATABASE=V:\Common\GL\Extract Files\2008\MC_1008.xls"

tdfLinked.SourceTableName = "AP_INPUT$"
dbsTemp.TableDefs.Append tdfLinked


'SQL = "INSERT INTO ta1 SELECT * FROM Test;"

DoCmd.RunCommand "INSERT INTO ta1 SELECT * FROM Test;"

End Sub

I get it on the "DoCmd.RunCommand" above...

Quote

Hi there... I sent you earlier two files; excel with worksheets and an access database. I am trying to load up multiple excel files with various worksheets. I would like to have those apprended to an access table, etc... I know I can link the excel files, but there are some 20 worksheets and the location chnages each year... An automated load would help quite a bit..

Thanks for your hep..

[thava]

hi

i am in frustated work that's why the bit of delay

make break point on the
"sql=" statement
now check the fields in the ta1 table and test table are same datatype

if not same try to convert the datatype according to your nedd using the query

like

select val(col1),col2, .... from tablename

instead of
select * from tableName

This post has been edited by thava: 03 November 2008 - 10:20 PM

[TA0523]

thava, on 3 Nov, 2008 - 09:08 PM, said:

hi

i am in frustated work that's why the bit of delay

make break point on the
"sql=" statement
now check the fields in the ta1 table and test table are same datatype

if not same try to convert the datatype according to your nedd using the query

like

select val(col1),col2, .... from tablename

instead of
select * from tableName

Quote

Hi Thava

Thanks for your note...

The following statement is working now ... ?

DoCmd.RunSQL "INSERT INTO ta1 SELECT * FROM Test;"

Thanks for all your help... !