TransferDatabase ToSQL

[GracieLou]

Good Morning Gurus,
I hope I am posting to the correct area. I was torn between Forms Coding
and here. But it is mainly about exporting to SQL.

I am using Access 2003 and SQL Server 2000.

I have a linked table I am taking into SQL via Access query.

DoCmd.TransferDatabase acExport, “ODBC Databaseâ€, “ODBC;DRIVER=SQL
Server;SERVER=(local); APP=Microsoft Office
2003;WSID=computername;DATABASE=LGL09;Trusted_Connection=Yes;
TABLE=dbo.tblMar;†& “DATABASE=LGLAllâ€, acTable, “qry002LglDataâ€, “Marchâ€

Although this works, I do get an error msg.

ODBC—call failed.
[Microsoft][ODBC SQL Server Driver][SQL Server]Warning: The table ‘March’
has been created but its maximum row size (14376) exceeds the maximum number
of bytes per row (8060). INSERT or UPDATE of a row in this table will fail
if the resulting row length exceeds 8060 bytes. (#1708)[Microsoft][ODBC SQL
Server Driver][SQL Server]There is already an object named ‘March’ in the
database. (#2714)

The table March gets created in the correct place and all of the records are
there.
The table that it creates and fills, March, has 30 fields. 28 of them are
text and 2 are currency. All of the text fields come across as nvarcar,
length 255.

Questions:
What can I do to get rid of the msg?
Can I do any coding to make the field type varcar?
And one last question; Can you append to a table in SQL from Access instead
of only creating a table like the above code does?
Any help is appreciated.

 

[Clifford Bass]

Hi Gracie Lou,

I would guess that the 28 text fields of 255 characters is the problem.
Since they are NVARCHAR, there are two bytes per character. That comes to
14,280 bytes minimum. There are always extra characters involved in a row
size. I am not familiar enough with SQL Server to be specific.

Perhaps it would make more sense to create the table directly in SQL
Server, specifying your field sizes appropriately. Then create links to it
in your Access database. Then you can use an append query to add records to
it. And of course, if you need to delete them, use a delete query. So the
answer to that is yes, you can append, update and delete. Make sure to
define a unique index/primary key.

There is one other thing you should consider. Your table name is
March. Unless this is a one-time thing, this is not good design. You are
storing information in the table name itself. Better to make a generic table
that includes, as part of the primary key, the month (and year), either as a
single date field or as a couple (small) integer fields, one for month and
one for year. To store a "month" in a date field, use the first day of the
month of the appropriate year. When printing, you can format as just the
month (and year if desired).

Hope that helps,

Clifford Bass

Good Morning Gurus,
I hope I am posting to the correct area. I was torn between Forms Coding
and here. But it is mainly about exporting to SQL.

I am using Access 2003 and SQL Server 2000.

I have a linked table I am taking into SQL via Access query.

DoCmd.TransferDatabase acExport, “ODBC Databaseâ€, “ODBC;DRIVER=SQL
Server;SERVER=(local); APP=Microsoft Office
2003;WSID=computername;DATABASE=LGL09;Trusted_Connection=Yes;
TABLE=dbo.tblMar;†& “DATABASE=LGLAllâ€, acTable, “qry002LglDataâ€, “Marchâ€

Although this works, I do get an error msg.

ODBC—call failed.
[Microsoft][ODBC SQL Server Driver][SQL Server]Warning: The table ‘March’
has been created but its maximum row size (14376) exceeds the maximum number
of bytes per row (8060). INSERT or UPDATE of a row in this table will fail
if the resulting row length exceeds 8060 bytes. (#1708)[Microsoft][ODBC SQL
Server Driver][SQL Server]There is already an object named ‘March’ in the
database. (#2714)

The table March gets created in the correct place and all of the records are
there.
The table that it creates and fills, March, has 30 fields. 28 of them are
text and 2 are currency. All of the text fields come across as nvarcar,
length 255.

Questions:
What can I do to get rid of the msg?
Can I do any coding to make the field type varcar?
And one last question; Can you append to a table in SQL from Access instead
of only creating a table like the above code does?
Any help is appreciated.

 

[GracieLou]

Thank you for your response.

I didn't think you could write to an SQL table from Access. I linked the
table from SQL to Access but when I create an append query that table does
not show up in the table list. I looked at using "Another Database" but
cannot browse to the SQL db. From everything I've read, you can Import,
Export, or link to SQL.

I did find how to limit the field size. using the same query I sent data
with to SQL, I created a table in Access of only 5 records. Seleted those
records and then opened the table in design view. I changed all of the
fields to their proper length and saved the table.

For some reason, now when I transfer the data it goes over with the lengths
shown in the test table I have in Access. I don't understand it but it works.

I tried writing VBA that opens the SQL data import wizard. But couldn't get
that to work either.

Would you happen to know if I can get a DTS package to run using VBA maybe
from a form?

Thanks

Hi Gracie Lou,

I would guess that the 28 text fields of 255 characters is the problem.
Since they are NVARCHAR, there are two bytes per character. That comes to
14,280 bytes minimum. There are always extra characters involved in a row
size. I am not familiar enough with SQL Server to be specific.

Perhaps it would make more sense to create the table directly in SQL
Server, specifying your field sizes appropriately. Then create links to it
in your Access database. Then you can use an append query to add records to
it. And of course, if you need to delete them, use a delete query. So the
answer to that is yes, you can append, update and delete. Make sure to
define a unique index/primary key.

There is one other thing you should consider. Your table name is
March. Unless this is a one-time thing, this is not good design. You are
storing information in the table name itself. Better to make a generic table
that includes, as part of the primary key, the month (and year), either as a
single date field or as a couple (small) integer fields, one for month and
one for year. To store a "month" in a date field, use the first day of the
month of the appropriate year. When printing, you can format as just the
month (and year if desired).

Hope that helps,

Clifford Bass

Good Morning Gurus,
I hope I am posting to the correct area. I was torn between Forms Coding
and here. But it is mainly about exporting to SQL.

I am using Access 2003 and SQL Server 2000.

I have a linked table I am taking into SQL via Access query.

DoCmd.TransferDatabase acExport, “ODBC Databaseâ€, “ODBC;DRIVER=SQL
Server;SERVER=(local); APP=Microsoft Office
2003;WSID=computername;DATABASE=LGL09;Trusted_Connection=Yes;
TABLE=dbo.tblMar;†& “DATABASE=LGLAllâ€, acTable, “qry002LglDataâ€, “Marchâ€

Although this works, I do get an error msg.

ODBC—call failed.
[Microsoft][ODBC SQL Server Driver][SQL Server]Warning: The table ‘March’
has been created but its maximum row size (14376) exceeds the maximum number
of bytes per row (8060). INSERT or UPDATE of a row in this table will fail
if the resulting row length exceeds 8060 bytes. (#1708)[Microsoft][ODBC SQL
Server Driver][SQL Server]There is already an object named ‘March’ in the
database. (#2714)

The table March gets created in the correct place and all of the records are
there.
The table that it creates and fills, March, has 30 fields. 28 of them are
text and 2 are currency. All of the text fields come across as nvarcar,
length 255.

Questions:
What can I do to get rid of the msg?
Can I do any coding to make the field type varcar?
And one last question; Can you append to a table in SQL from Access instead
of only creating a table like the above code does?
Any help is appreciated.

 

[Clifford Bass]

Hi Gracie Lou,

Linked tables should show along with local tables when you create a
query. And they are pretty much usable just like local tables. That is, as
long as you have the appropriate permissions on them in SQL Server. How are
you linking it? Did you create an ODBC DSN (Data Source Name) using the
Windows ODBC Data Source Administrator that connects to your SQL Server
database? Did you then use that DSN to link to an ODBC Database?

You can invoke external programs from VBA. But I see no reason to even
bother with them when Access easily deals with data imports and exports. And
is usable as what is called a front end to many other types of databases.

Again, the better way to do this is to create the table in SQL Server
first. Then link to it from Access. Then use append queries to transfer the
data from Access to SQL Server.

Clifford Bass

 

[GracieLou]

Clifford,

Thanks again for responding. I know this is taking up your time.

I link the table that resides in SQL Server 2000. File, Get External Data,
ODBC, use a DSN created the first time, and the table links. I can read it
in Access. But, when I create a query and try to make it an append query,
the dbo_Test does not show up in the list of tables I can append to. I can
see other linked tables but they are from other Access db's.

Would the connection be different if I used the Windows ODBC Data Source
Administrator as was in your last reply? Is this different than the
connection I have as shown above?

Thanks

 

[GracieLou]

Clifford,

Okay. I don't know why this worked but it did.

I renamed the linked table from dbo_Test to Test and now I can see it to
select to append to. It worked.

Thanks for all of your help.

 

[Clifford Bass]

Hi Gracie Lou,

When Access gives you the option to create a DSN, it is using the
Windows ODBC Data Source Administrator. Your original example had used a
recordset in code, so I was not sure quite what you were doing and needed to
be sure.

It is odd that renaming the table made a difference. It should not
matter. Oh well, as long as it is working now, that is all that matters.

Glad to help and you are quite welcome!

Clifford Bass